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.