add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
CREATE TABLE mytable (id, col) AS
SELECT 1, 0 FROM DUAL UNION ALL
SELECT 2, 0 FROM DUAL UNION ALL
SELECT 3, 0 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 6, 1 FROM DUAL UNION ALL
SELECT 7, 0 FROM DUAL UNION ALL
SELECT 8, 0 FROM DUAL UNION ALL
SELECT 9, 1 FROM DUAL UNION ALL
SELECT 10, 0 FROM DUAL;
10 rows affected
SELECT id, col, grp
FROM mytable
MATCH_RECOGNIZE(
ORDER BY id
MEASURES MATCH_NUMBER() AS grp
ALL ROWS PER MATCH
PATTERN (same_col+)
DEFINE same_col AS FIRST(col) = col
);
ID COL GRP
1 0 1
2 0 1
3 0 1
4 1 2
5 1 2
6 1 2
7 0 3
8 0 3
9 1 4
10 0 5
SELECT id, col,
SUM(has_changed) OVER (ORDER BY id) AS grp
FROM (
SELECT t.*,
CASE LAG(col) OVER (ORDER BY id) WHEN col THEN 0 ELSE 1 END AS has_changed
FROM mytable t
)
ID COL GRP
1 0 1
2 0 1
3 0 1
4 1 2
5 1 2
6 1 2
7 0 3
8 0 3
9 1 4
10 0 5