x

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...

more ▼

asked Aug 30, 2014 at 10:04 PM in Default

avatar image

chopkin
22 1 2 5

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.

Sep 03, 2014 at 09:37 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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!

more ▼

answered Aug 30, 2014 at 10:45 PM

avatar image

JohnM
14.2k 3 7 14

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Sep 02, 2014 at 09:23 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x159
x76

asked: Aug 30, 2014 at 10:04 PM

Seen: 457 times

Last Updated: Sep 03, 2014 at 09:37 AM

Copyright 2017 Redgate Software. Privacy Policy