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...
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
myCTE.id and x.theDate = myCTE.theDate GO Hope this helps!
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