By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY, flag INTEGER NOT NULL);
INSERT INTO test(flag) VALUES
(0),(0),(1),(0),(1),(0),(1);
SELECT * FROM test;
| id | flag |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
| 4 | 0 |
| 5 | 1 |
| 6 | 0 |
| 7 | 1 |
SELECT id id1, CASE WHEN flag THEN next END id2
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY flag ORDER BY id) rn,
MIN(CASE WHEN flag THEN id END) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) next
FROM test
)
WHERE flag = 0 OR rn % 2 = 1
ORDER BY id1
| id1 | id2 |
|---|---|
| 1 | null |
| 2 | null |
| 3 | 5 |
| 4 | null |
| 6 | null |
| 7 | null |