Getting newest record for a person with multiple

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Getting newest record for a person with multiple

Author

Topic

merrittr
Starting Member

10 Posts

Posted - 2013-07-04 : 18:00:58

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

549 Posts

Posted - 2013-07-04 : 21:51:52

[CODE]

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;

[/CODE]

merrittr
Starting Member

10 Posts

Posted - 2013-07-05 : 10:35:41

HOLY CRAP!

that worked like a hot damn!
thanks MuMu
here is a happy face for you:

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-06 : 23:39:20

Glad to help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources