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 2005 Forums
 Transact-SQL (2005)
 Help retrieving most recent records - group by?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
422 Posts
Posted - 01/08/2013 :  16:01:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 01/08/2013 :  16:26:01  Show Profile  Visit webfred's Homepage  Reply with Quote
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
  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.03 seconds. Powered By: Snitz Forums 2000