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.
Go to Top of Page
   

- Advertisement -