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…

[ThreadNextMessageId] = LEAD(gm.GroupMessageId, 1) OVER(PARTITION BY gm.ThreadId ORDER BY gm.GroupMessageId)

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.

WITH cte AS (
    SELECT
        gm.GroupMessageId
        ,gm.ThreadId
        ,[ThreadNextMessageId] = LEAD(gm.GroupMessageId) OVER(PARTITION BY gm.ThreadId ORDER BY gm.GroupMessageId)
        ,[ThreadPrevMessageId] = LAG(gm.GroupMessageId) OVER(PARTITION BY gm.ThreadId ORDER BY gm.GroupMessageId)
    FROM dbo.GroupMessage gm
)
SELECT 
    cte.GroupMessageId
    ,cte.ThreadId
    ,cte.ThreadNextMessageId
    ,cte.ThreadPrevMessageId
    ,[ThreadNextOffset] = (SELECT COUNT(*) FROM cte c1 WHERE c1.GroupMessageId >= cte.GroupMessageId AND c1.GroupMessageId < cte.ThreadNextMessageId)
    ,[ThreadPrevOffset] = (SELECT COUNT(*) FROM cte c1 WHERE c1.GroupMessageId >= cte.ThreadPrevMessageId AND c1.GroupMessageId < cte.GroupMessageId)
FROM cte
ORDER BY cte.GroupMessageId

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?

Answer

I would suggest enumerating all the rows and using arithmetic:

WITH gm AS (
      SELECT gm.*,
             ROW_NUMBER() OVER (ORDER BY GroupMessageId) as seqnum
      FROM dbo.GroupMessage gm
     )
SELECT gm.*,
       (LEAD(seqnum) OVER (PARTITION BY gm.ThreadID ORDER BY gm.GroupMessageId) - seqnum - 1) as messages_in_between
FROM gm;
ORDER BY gm.GroupMessageId

Advertisement

Answer