By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (
`id` INTEGER,
`value` INTEGER
);
✓
INSERT INTO tablename
(`id`, `value`)
VALUES
('1', '1'),
('2', '2'),
('3', '3'),
('4', '5'),
('5', '6'),
('6', '8'),
('7', '9');
✓
select id, value, sum(flag) over (order by id) grp
from (
select *, coalesce(value <> lag(value) over (order by id) + 1, 1) flag
from tablename
)
| id | value | grp |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | 5 | 2 |
| 5 | 6 | 2 |
| 6 | 8 | 3 |
| 7 | 9 | 3 |