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:
If: current row's id is different from the previous row's id, use current row's row_order as the new_order value
If: current row's Type is "start" use the current row's row_order as the new_order
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
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.