clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591582 fiddles created (45740 in the last week).

CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL); ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)