Vadim Shkaberda Vadim Shkaberda - 4 months ago 9
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

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;
Comments