Help retrieving most recent records - group by? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Help retrieving most recent records - group by?

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-01-08 : 16:01:48

I have an ACTIVITY table that contains the following columns: CONTACT, NOTE, ACTIVITY_DATE.

A contact can be listed multiple times, each time with a different note and activity date.

How can I retrieve each contact and their most recent note only?


TABLE = ACTIVITY 

CONTACT		NOTE		ACTIVITY_DATE
Jack		Test 1A		2012-12-25 10:33:11.840
Jack		Test 1B 	2013-01-07 04:20:00.005
Jack		Test 1C		2013-01-07 11:30:00.999
Jill		Test 2A		2010-04_10 08:22:00.222
Jill		Test 2B		2011-06_11 18:24:22.777
Jill		Test 2C		2012-09_29 01:02:33.666

DESIRED RESULTS

Jack		Test 1C		2013-01-07 11:30:00.999
Jill		Test 2C		2012-09_29 01:02:33.666

Thanks in advance!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-08 : 16:26:01

select * from
(select row_number() over(partition by CONTACT order by ACTIVITY_DATE desc) as rnum, * from ACTIVITY )dt
where rnum=1

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources