mbergstrom (5/29/2009)
How does the ROW_NUMBER() approach compare with using self-joins as follows?
SELECT Previous.Category,
Previous.Date,
Next.Date
FROM Dates.CategoryDate Previous
LEFT JOIN Dates.CategoryDate Next
ON Previous.Category = Next.Category
AND Previous.Date < Next.Date
LEFT JOIN Dates.CategoryDate Middle
ON Previous.Category = Middle.Category
AND Previous.Date < Middle.Date
AND Middle.Date < Next.Date
WHERE Middle.Category IS NULL;
Take a look at the execution plans. The self-joins result in one hefty Hash Match join. This join is helpful if one of the tables is much smaller than the other. In this particular case, the tables are not so small. I don't understand the purpose of "Middle." Overall, the self-joins take a lot longer than ROW_NUMBER() for both indexed and non-indexed versions of the table.