AlexT (11/13/2008)
that supposed to be faster, if clustered index applied on (PersonID, Version )select distinct a.PersonID,a.Version, a.DEDate , case when b.PersonID is null then 0 else 1 end AS [Is ImProper]
from PersonRecord a
left join (
select a.PersonID, a.Version
from PersonRecord a
inner join PersonRecord b on a.PersonID = b.PersonID and a.Version>b.Version and a.DEDate<b.DEDate
) b on a.PersonID = b.PersonID and a.Version=b.Version
thanks
Good point about the fact that Version is part of the clustered index.
Unfortunately, incorporating version into the subselct breaks the solution. It no longer returns the desired results.