SQL Lag Increment By 1

Question

I am trying to use the LAG function to add 1 to every row, with '1' as the default value. For example;

SELECT 
    T.NAME,
    T.SESSION,
    LAG (CASE WHEN T.SESSION = 2023 THEN '1' END, 1, 0) OVER (ORDER BY T.NAME) + 1 AS 'SESSION_ORDER'
FROM 
    TABLE T 
WHERE 
    T.SESSION >= 2023

The query would return 4 rows, and what I'm after is to set the default value of the first row to 1 in the derived column SESSION_ORDER, then for the subsequent rows, increment by 1 each time so the next three rows would be;

2
3
4

The problem I'm having is that the results are as follows;

1
2
NULL
NULL

UPDATE: if anyone would like to run the query, or a similar one, I have used the demo select query from sqlliteonline.com;

SELECT 
    id, name,
    LAG (CASE WHEN name = 'CREATE' THEN 1 END, 1, 0) OVER (ORDER BY ID) + 1
FROM 
    demo
WHERE 
    NAME IN ('CREATE', 'SELECT', 'INSERT', 'UPDATE');

The output is:

1
2
NULL
NULL

Answer 1

Here's how you can modify your original query using ROW_NUMBER:

SELECT 
T.NAME,
T.SESSION,
ROW_NUMBER() OVER (ORDER BY T.NAME) AS 'SESSION_ORDER'
FROM TABLE T 
WHERE T.SESSION >= 2023;

For the demo query on sqlliteonline.com:

SELECT 
id,
name,
ROW_NUMBER() OVER (ORDER BY ID) AS 'SESSION_ORDER'
FROM demo
WHERE NAME IN ('CREATE','SELECT','INSERT','UPDATE');

This will give you a sequence starting from 1 and incrementing by 1 for each row in the result set, based on the specified ORDER BY clause.