Most Recent Date with Most Recent Table Id

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

Most Recent Date with Most Recent Table Id

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2014-07-15 : 18:52:33

I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column. Any help would be appreciated.

Below is the code without the Max ID column logic

SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records 
FROM table1
GROUP BY tCustID

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-07-15 : 19:13:27

[code]select a.tCustId, a.tDate
from (
select tCustId,tDate, row_number() over(partition by tCustId order by tDate DESC, Id DESC) rownum
from Table1
) a
where
a.rownum = 1[/code]

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Brittney10
Posting Yak Master

154 Posts

Posted - 2014-07-15 : 21:54:26

Thanks! That worked perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources