Firebird/SQL: Find holes in a sequence

CREATE TABLE GAP( COUNTER INTEGER PRIMARY KEY ); insert into gap (counter) values (1); insert into gap (counter) values (2); --::::: 3 --::::: 4 insert into gap (counter) values (5); insert into gap (counter) values (6); --::::: 7 insert into gap (counter) values (8); insert into gap (counter) values (9); insert into gap (counter) values (10); --===== here the trick : SELECT list(CASE WHEN N2-N1-2 > 0 THEN (N1+1) || '-'|| (N2-1) ELSE CAST(N1+1 AS VARCHAR(10)) END) Missing FROM ( SELECT a.counter N1, (SELECT counter FROM gap WHERE counter > a.counter rows 1) N2 FROM gap a ) B WHERE N2-N1 > 1; -- results: Missing ======= 3-4,7
Get gaps in data (holes in sequence)

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.