Get first record per identifier group in SQL
Code Description Whatever
---------------------------------
1 stuff blah
1 something meh
2 yah bong
2 never hammer time
How do I get a results set from this with each Code
only present once? (I don't overly care which record for that code it is).
So I want....
1 stuff blah
2 yah 开发者_如何学C bong
SELECT *
FROM (
SELECT * , row_number() over(partition by code order by Description) as id
from yourTable
) temp
WHERE id = 1
I think this is sql server only
You first need to pick a column which determines what counts as 'the first result'. In my example I chose Description
:
SELECT * FROM YourTable first
WHERE
(SELECT COUNT(*) FROM YourTable previous
WHERE previous.Code=first.Code AND previous.Description < first.Description) = 0
精彩评论