Suppose I have the following table (Date CustNum is an unique index)

The table has ~500K records.

What is the best method to get the previous and next rowid of the CustNum?

I've tried to use sub-query but I have faced a performance issue.

SELECT T1.*,
    (SELECT TOP 1 RowID FROM T T2 WHERE T2.CustNum = T1.CustNum AND T2.Date < T1.Date ORDER BY DATE DESC) AS CustPrevRowId,
    (SELECT TOP 1 RowID FROM T T2 WHERE T2.CustNum = T1.CustNum AND T2.Date > T1.Date ORDER BY DATE ) AS CustNextRowId
 FROM T T1

CodePudding user response：

As already pointed in the comments, you can use the two window functions:

• LAG, retrieves the previous row in the same partition, given a specified order
• LEAD, does the same, but will get the following row instead

In this specific case, you want to:

• partition on "CustNum" (since you want last row for each customer number)
• order by the date field (so that it will attempt to get rowid with respect to last/next date)

SELECT *, LAG([RowId]) OVER(PARTITION BY [CustNum] ORDER BY [Date]) AS CustPrevRowId,
          LEAD([RowId]) OVER(PARTITION BY [CustNum] ORDER BY [Date]) AS CustNextRowId
FROM tab
ORDER BY RowId

Check the demo here.

Note: the last ORDER BY RowId clause is not necessary.