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.
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 table1GROUP BY tCustID |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-07-15 : 19:13:27
|
[code]select a.tCustId, a.tDatefrom ( select tCustId,tDate, row_number() over(partition by tCustId order by tDate DESC, Id DESC) rownum from Table1 ) awhere 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! |
|
|
|
|
|