Skip to content
Advertisement

How do you get the size of the gap from LEAD or LAG?

In my table, I have messages grouped by threads. Using LEAD, I can get the next message within a thread…

But how do I tell how many rows are between the current row and the next row in the thread when ordering by GroupMessageId? The table will likely contain rows from other threads in between and I want to count those as well.

I found one way that seems to do this by enclosing my query as a CTE and then using the ID produced by LEAD to count the number of rows between the current GroupMessageId and ThreadNextMessageId, but it seems like there should be a more efficient way.

Is there another method for counting rows between the current one and ones produced by LEAD or LAG that doesn’t require a CTE – perhaps using some other SQL window function?

Advertisement

Answer

I would suggest enumerating all the rows and using arithmetic:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement