SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Getting newest record for a person with multiple
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

merrittr
Starting Member

10 Posts

Posted - 07/04/2013 :  18:00:58  Show Profile  Reply with Quote
Hi I have a data set like this

person1,98,12/04/2010
person1,99,09/20/2011
person2,78,10/12/2001
person2,54,11,20,2005

but what I want is
person1,99,09/20/2011
person2,54,11,20,2005

the the record with the latest data on it how would I do this

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/04/2013 :  21:51:52  Show Profile  Reply with Quote



DECLARE @Temp TABLE (Name VARCHAR(10), ID INT, [Date] DATE);

INSERT INTO @Temp VALUES
('Person1', 99, '2013-07-20'),
('Person1', 88, '2013-06-25'),
('Person2', 70, '2013-08-20'),
('Person2', 88, '2013-06-25');


SELECT Name, ID, [Date] FROM 
 (SELECT  Name, ID, [Date], ROW_NUMBER() 
              OVER(PARTITION BY Name order by [Date] DESC) AS RN from @Temp) T
 WHERE T.RN = 1;


Go to Top of Page

merrittr
Starting Member

10 Posts

Posted - 07/05/2013 :  10:35:41  Show Profile  Reply with Quote
HOLY CRAP!

that worked like a hot damn!
thanks MuMu
here is a happy face for you:
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/06/2013 :  23:39:20  Show Profile  Reply with Quote
Glad to help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000