By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL);
ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);
INSERT INTO GapsIslands
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 1, 8 UNION ALL
SELECT 1, 9 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 1, 20 UNION ALL
SELECT 1, 21 UNION ALL
SELECT 1, 25 UNION ALL
SELECT 1, 26;
12 rows affected
SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
, CASE WHEN EXISTS ( SELECT *
FROM GapsIslands AS b
WHERE b.ID = a.ID
AND b.SeqNo = a.SeqNo - 1 )
THEN 'Exists'
ELSE 'Not exists'
END AS previous_SeqNo_exists
FROM GapsIslands AS a
ORDER BY SeqNo
| ID | SeqNo | RowNum | previous_SeqNo_exists |
|---|---|---|---|
| 1 | 1 | 1 | Not exists |
| 1 | 2 | 2 | Exists |
| 1 | 5 | 3 | Not exists |
| 1 | 6 | 4 | Exists |
| 1 | 8 | 5 | Not exists |
| 1 | 9 | 6 | Exists |
| 1 | 10 | 7 | Exists |
| 1 | 12 | 8 | Not exists |
| 1 | 20 | 9 | Not exists |
| 1 | 21 | 10 | Exists |
| 1 | 25 | 11 | Not exists |
| 1 | 26 | 12 | Exists |
SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
FROM GapsIslands AS a
WHERE NOT EXISTS (
SELECT *
FROM GapsIslands AS b
WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1)
| ID | SeqNo | RowNum |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 5 | 2 |
| 1 | 8 | 3 |
| 1 | 12 | 4 |
| 1 | 20 | 5 |
| 1 | 25 | 6 |