SQL query to select distinct row with minimum value

Question

I want an SQL statement to get the row with a minimum value    Consider this table   id  game   point 1    x      5 1    z      4 2    y      6 3    x      2 3    y      5 3    z      8   How do I select the ids that have the minimum value in the point column  grouped by game   Like the following   id  game   point     1    z      4 2    y      5 3    x      2

User · Answer

SELECT DISTINCT  FIRST VALUE ID  OVER  Partition by Game ORDER BY Point  AS ID  Game  FIRST VALUE Point  OVER  Partition by Game ORDER BY Point  AS Point FROM  T

User · Answer

Try   select id  game  min point  from t group by id

User · Answer

This alternative approach uses SQL Server s OUTER APPLY clause  This way  it   creates the distinct list of games  and fetches and outputs the record with the lowest point number for that game    The OUTER APPLY clause can be imagined as a LEFT JOIN  but with the advantage that you can use values of the main query as parameters in the subquery  here  game    SELECT colMinPointID FROM     SELECT game   FROM table   GROUP BY game   As rstOuter OUTER APPLY     SELECT TOP 1 id As colMinPointID   FROM table As rstInner   WHERE rstInner game   rstOuter game   ORDER BY points   AS rstMinPoints

User · Answer

This is another way of doing the same thing  which would allow you to do interesting things like select the top 5 winning games  etc     SELECT    FROM         SELECT ROW NUMBER   OVER  PARTITION BY ID ORDER BY Point  as RowNum         FROM Table    X   WHERE RowNum   1   You can now correctly get the actual row that was identified as the one with the lowest score and you can modify the ordering function to use multiple criteria  such as  Show me the earliest game which had the smallest score   etc

User · Answer

Ken Clark s answer didn t work in my case  It might not work in yours either  If not  try this   SELECT    from table T  INNER JOIN       select id  MIN point  MinPoint   from table T   group by AccountId     NewT on T id   NewT id and T point   NewT MinPoint  ORDER BY game desc

User · Answer

As this is tagged with sql only  the following is using ANSI SQL and a window function   select id  game  point from     select id  game  point            row number   over  partition by game order by point  as rn   from games   t where rn   1

User · Answer

SELECT   from room INNER JOIN       select DISTINCT hotelNo  MIN price  MinPrice   from room  Group by hotelNo     NewT     on room hotelNo   NewT hotelNo and room price   NewT MinPrice

User · Answer

This is portable - at least between ORACLE and PostgreSQL  select t   from table t  where not exists select 1 from table ti where ti attr  gt  t attr

User · Answer

This will work  select   from table  where  id point  IN  select id min point  from table group by id

User · Answer

Use   SELECT tbl   FROM TableName tbl   INNER JOIN         SELECT Id  MIN Point  MinPoint     FROM TableName     GROUP BY Id     tbl1   ON tbl1 id   tbl id WHERE tbl1 MinPoint   tbl Point

[sql] SQL query to select distinct row with minimum value

Examples related to sql

Examples related to database

Examples related to greatest-n-per-group