SQL - Is there a way to access the previous row in the same column that's currently being created? Similar to Peek() function in QLIK

Question

So, I've looked into this a bit already, but I can't seem to find a way to access the previous row in the SAME column that is CURRENTLY being created in the SELECT statement. I understand that I can use lag() to access the previous row's value for another existing column, but not for the one being created.

Essentially, my goal is to:

1. If: current row's id is different from the previous row's id, use current row's row_order as the new_order value

2. If: current row's Type is "start" use the current row's row_order as the new_order

3. Else: Use the previous row's row_order as the new_order

For example, in this example code, the result is almost correct, but not quite on result lines 7-8.This is because I'm not actually accessing the previous row in the same column, I'm joining an identical table that's offset by one row. Instead, the [new_order] column for lines 7-8 should both be "5".

Current output

row_order   id  Type    new_order
1           11  start   1
2           11  go      1
3           11  start   3
4           11  go      3
5           12  start   5
6           12  go      5
7           12  go      6
8           12  go      7

Desired output

row_order   id  Type    new_order
1           11  start   1
2           11  go      1
3           11  start   3
4           11  go      3
5           12  start   5
6           12  go      5
7           12  go      5
8           12  go      5

DECLARE @t TABLE (row_order INT, id INT,  Type VARCHAR(100));
INSERT INTO @t VALUES
(1, 11, 'start'),
(2, 11, 'go'),
(3, 11, 'start'),
(4, 11, 'go'),
(5, 12, 'start'),
(6, 12, 'go'),
(7, 12, 'go'),
(8, 12, 'go');

WITH rcte AS (
    SELECT 
        *
        ,row_order AS new_order
    FROM @t
    WHERE row_order = 1
    UNION ALL
    SELECT 
        curr.* 
        ,CASE
            WHEN curr.id <> prev.id THEN curr.row_order
            WHEN curr.Type = 'start' THEN curr.row_order
            ELSE prev.row_order
        END AS new_order
    FROM @t AS curr
    JOIN rcte AS prev ON curr.row_order = (prev.row_order + 1)
)
SELECT *
FROM rcte

I was able to do this Qlik's data load editor, because they have this peek() function, but I need to find a way to do this in sql https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/InterRecordFunctions/Peek.htm

Answer 1

Given your expected output, I don't really think it's necessary to look back at the previous row where type='start' using lag.

If you want to use a recursive CTE like you're currently doing, I suggest something like:

WITH rcte AS (
    SELECT 
        *
        ,row_order new_order
    FROM @t
    WHERE type = 'Start'
    UNION ALL
    SELECT 
        curr.* 
        ,prev.new_order
    FROM @t curr
    JOIN rcte prev ON curr.row_order = prev.row_order + 1
    WHERE curr.type <> 'Start'
)
SELECT *
FROM rcte
ORDER BY row_order

Rather than starting from the first row and recursing forward (like your current code), this gets all the type='start' rows and then adds the next non-'start' row. There's no need to actually figure out what the previous 'start' row was, because the previous non-start row already has the correct new_order.

You can see it working here (along with another option).

If your data really is a simple as in your example, I might prefer something like:

WITH starts AS
(SELECT *
 FROM @t
 WHERE type='Start')
SELECT t.*, max(starts.row_order) new_order
FROM @t t INNER JOIN starts ON t.row_order >= starts.row_order
GROUP BY t.row_order, t.id, t.type

I don't dislike the recursive CTE, but I personally prefer the second option here.