Selecting only the most recent record

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)

Selecting only the most recent record

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2009-09-17 : 11:41:26

This shouldn't be hard but I can't figure it out. I have a financial table which includes all purchase transactions made by all clients. I just want to pull a recordset of only the single most recent transaction of each client. How can I do this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 11:44:35

select * from
(
select row_number() over (partition by clientId order by purchase_date desc) as rownum,
column1,
column2,
...
from table
where ...
)dt
where rownum = 1

No, you're never too old to Yak'n'Roll if you're too young to die.