how to select one row from several rows with minimum value

Question

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.

score 5 · Accepted Answer · answered Jul 19 '16 at 11:28

5

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;

answered Jul 19 '16 at 11:28

Gordon Linoff

1,242,037
58
646
786

Thank you! Wonderful. That's a discovery for me. – Vadim Shkaberda Jul 19 '16 at 11:33
What if I have an additional field I want to partition by... For examle, `filial`. Is there more elegant solution except `partition by cast(id as varchar(MAX)) + cast(filial as varchar(MAX))`? – Vadim Shkaberda Jul 20 '16 at 09:22
@VadimShkaberda . . . `partition by id, filial order by . . . `. – Gordon Linoff Jul 21 '16 at 01:56
Thank you for the answer. Strangely enough that `partition by id, filial order by` gave me an error first time I tried it. Now it's Ok. – Vadim Shkaberda Jul 21 '16 at 06:43

score -1 · Answer 2 · answered Jul 19 '16 at 11:43

We assume that all point entries are distinct(for each id and it's game so we can obtain the minimum of each id with it's game), Using a subquery and an inner join with two conditions would give you the result you,re waiting for.If it doesnt work with you I got another solution :

SELECT yt.*,     
FROM Yourtable yt INNER JOIN
    (
        SELECT ID, MIN(point) MinPoint
        FROM Yourtable
        GROUP BY ID
    ) t ON yt.ID = t.ID AND yt.Record_Date = yt.MinDate

how to select one row from several rows with minimum value

2 Answers2