I have SQL table with columns COL_1, COL_2:
| COL_1 | COL_2 | COL_3 |
|---|---|---|
| a | 2021-09-11 14:25:15.699 | 1 |
| b | 2021-09-11 14:25:36.489 | 2 |
| a | 2021-09-11 14:33:36.500 | 3 |
| a | 2021-09-11 14:36:24.915 | 3 |
| a | 2021-09-11 14:36:38.900 | 3 |
| a | 2021-09-11 14:36:54.905 | 3 |
| c | 2021-09-11 14:37:09.092 | 4 |
| a | 2021-09-11 14:39:09.111 | 5 |
| d | 2021-09-11 14:39:30.292 | 6 |
I need to get COL_3. I got column 4 by grouping column 1 and sorting by date (column 2), if the group changes, I assign a new index for the following groups.
This is a classic gaps-and-islands problem.
There are many solutions. A common one is to use LAG to identify rows which change value, then a conditional windowed COUNT to assign the group numbers
WITH PrevValues AS (
SELECT *,
LAG(COL_1) OVER (ORDER BY COL_2) AS PrevCol1
FROM YourTable t
)
SELECT COL_1,COL_2,COL_3,
COUNT(CASE WHEN PrevCol1 = COL_1 THEN NULL ELSE 1 END)
OVER (ORDER BY COL_2 ROWS UNBOUNDED PRECEDING) AS New_Col_3
FROM PrevValues t