Vadim Shkaberda Vadim Shkaberda - 1 year ago 62
SQL Question

how to select one row from several rows with minimum value

The question based on SQL query to select distinct row with minimum value.
Consider the table:

id game point
1 x 1
1 y 10
1 z 1
2 x 2
2 y 5
2 z 8


Using suggested answers from mentioned question (select the ids that have the minimum value in the point column, grouped by game) we obtain

id game point
1 x 1
1 z 1
2 x 2


The question is how to obtain answer with single output for each ID. Both outputs

id game point
1 x 1
2 x 2


and

id game point
1 z 1
2 x 2


are acceptable.

Answer Source

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by point asc) as seqnum
      from t
     ) t
where seqnum = 1;