CODEWITHSUNDEEP
sqlsql-server
Vadim Shkaberda
Vadim Shkaberda

Reputation: 2936

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.

Upvotes: 2

Views: 424

Answers (2)

Azizi
Azizi

Reputation: 1

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

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 5

Related Questions