Tag Info

New answers tagged


Since you are in 2012 version, here's another way to solve this gaps-and-islands problem that uses the new LAG() function: ; WITH seq AS ( SELECT SiteIDNumber, SequenceNumber, LagSequenceNumber = LAG(SequenceNumber) OVER (PARTITION BY SiteIDNumber ORDER BY SequenceNumber), ...


The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() ...

Top 50 recent answers are included