question

chopkin avatar image
chopkin asked

Select id, min(date) plus get sysident

MS SQL Server 2012 Table X Sysident ID Date 1 100 2014-01-01 2 100 2014-01-02 3 200 2014-02-01 4 200 2014-020-5 Desired output Sysident ID Date 1 100 2014-01-01 3 200 2014-02-01 So for each unique ID, I want to find the oldest date AND the Sysident of the row with oldest date. I can get the ID and oldest date with a select ID, min(date) group by ID but I cannot figure out how to get the corresponding Sysidents Thanks...
selectaggregates
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site operates by you casting votes. For each answer below that is helpful, indicate this by clicking on the thumbs up next to that answer. If any one answer below lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
JohnM avatar image
JohnM answered
Here's one possible way to do it: CREATE TABLE #x (sysident INT, id INT, theDate DATE) INSERT #x (sysident, id, theDate) SELECT 1, 100, '2014-01-01' UNION SELECT 2, 100, '2014-01-02' UNION SELECT 3, 200, '2014-02-01' UNION SELECT 4, 200, '2014-02-05' ;WITH myCTE (id, theDate) as (SELECT ID, MIN(thedate) FROM #x GROUP BY ID) SELECT x.sysident, myCTE.id, myCTE.theDate FROM #x x INNER JOIN myCTE ON x.id = myCTE.id and x.theDate = myCTE.theDate GO Hope this helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Nothing wrong with @JohnM 's answer, but here's another way select sysident, id, thedate from ( select rn=row_number()over(partition by id order by theDate asc), sysident, id, theDate from #x ) GroupedData where rn = 1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.