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