Steven Willis (4/5/2013)
Lynn Pettis (4/5/2013)
If you have duplicate dates, this fails. You will want to change RANK() to ROW_NUMBER().Good catch Lynn. But I don't think just changing to ROW_NUMBER fixes the tie-break problem. It just returns one row and who knows what the tie-breaker is? So whether using RANK, ROW_NUMBER, or perhaps DENSE-RANK, I should have added a tie-breaker to the ORDER BY like this:
RANK() OVER (ORDER BY EndDate DESC, ID DESC) AS [Rank]
This would give the tie-break to the most recently added row...but other ways of breaking the tie might be better such as a full DATETIME2 date.
ROW_NUMBER will work fine as the requirement really only needs the top 2 records. If there is a tie you still only want the top 2. DENSE_RANK didn't work either with the limited data sample. Give it a try with a tie on the latest dates.