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    

Answer 1

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 

Answer 2

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.