Duplicates using MAX Date Self-Join

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

Duplicates using MAX Date Self-Join

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-02-06 : 07:48:06

Hi

Got a data set like this:


rowID   PersonID   Start Date   End Date
=====   ========   ==========   ==========
001     6575556    19/06/2013   09/07/2013
001     6575556    20/06/2013   12/07/2013
001     6575556    21/06/2013   12/07/2013
002     9478522    15/05/2013   18/05/2013
003     7753423    22/08/2013   01/09/2013

Person can have more than one start/end date therefore I get multiple of the same row ID and Person ID when looking at their dates.

I want to display the most recent end date and associated data if there is more than one start/end date for the same person. I decided to do a self join with max Date aggregate using this against a main select from the Table1:


SELECT PersonID,
  MAX([End Date]) AS MaxEndDate
FROM Table1
GROUP BY
PersonID

And join it this way:


select RowID,
PersonID,
[End Date]
FROM Table1 INNER JOIN (
SELECT PersonID,
MAX([End Date]) AS MaxEndDate
FROM Table1
GROUP BY
PersonID 
) a
ON 
Table1.PersonID = a.PersonID
AND Table1.[end Date] = a.MaxEndDate

When I run the sub-query on its own it gives me the single PersonID and Max Date but on self-joining with Table1 I still get the duplicates values.

Any thoughts what is wrong with the statement?

Thanks

G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-02-06 : 11:28:50

In your example data you can see that there are two rows with the same max end date. That is the reason why you get duplicates.
Another way you can use is row_number()

select RowID,PersonID,[End Date]
FROM
(
select
row_number() over (partition by PersonID order by [End Date] desc) as rn,
RowID,
PersonID,
[End Date]
FROM Table1 )dt
where rn = 1

Too old to Rock'n'Roll too young to die.
edit: typo

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-02-06 : 11:36:27

quote:
Originally posted by webfred

In your example data you can see that there are two rows with the same max end date. That is the reason why you get duplicates.
Another way you can use is row_number()

select RowID,PersonID,[End Date]
FROM
(
select
row_number() over (partition by PersonID order by [End Date] desc) as rn,
RowID,
PersonID,
[End Date]
FROM Table1 )dt
where rn = 1

Too old to Rock'n'Roll too young to die.
edit: typo

I had just remembered using this before as well for a similar problem, appreciate the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources