Select Most Recent Record Per User

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

General SQL Server Forums

New to SQL Server Programming

Select Most Recent Record Per User

Author

Topic

WallyHartshorn
Starting Member

1 Post

Posted - 2010-06-29 : 11:31:04

Suppose you have a table containing 3 non-unique columns: userId, action, actionDate.

How could you select the most recent record for each user? For example:

Given this data:

Joe   Eat    5/1/2010
Joe   Sleep  5/2/2010
Erin  Jog    5/2/2010
Erin  Walk   5/3/2010

You would need to get this result

Joe   Sleep  5/2/2010
Erin  Walk   5/3/2010

I'm stuck! Any help would be greatly appreciated!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-29 : 11:43:52

[code]select userId, action, actionDate
from
(
select row_number() over(partition by userId order by actionDate desc) as seq, * from TableA
) T
where seq = 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources