CODEWITHSUNDEEP
sqlsql-servert-sqlselectgreatest-n-per-group
Aegletes
Aegletes

Reputation: 490

Getting the row with the smallest column value if another column is the same in SQL Server

Id  Number  Value
------------------
1     6       1
2     6       2
3     9       2

I have a table like this and basically, I need to return the row with the smallest Value if their Number columns are the same, the example result I need is as below:

Id  Number  Value
-----------------
1     6       1
3     9       2

How do I achieve this in T-SQL?

Upvotes: 2

Views: 359

Answers (1)

Mureinik
Mureinik

Reputation: 311393

You could use the rank window function:

SELECT id, number, value
FROM   (SELECT id, number, value, RANK() OVER(PARTITION BY number ORDER BY value) AS rk
        FROM   mytable)
WHERE  rk = 1

EDIT:
As noted in the comments, if there is more than one line with the lowest value, using rank would return both. If you want to return just one of them, you should use row_number instead:

SELECT id, number, value
FROM   (SELECT id, number, value,
               ROW_NUMBER() OVER(PARTITION BY number ORDER BY value) AS rn
        FROM   mytable)
WHERE  rn = 1

Upvotes: 2

Related Questions