Re: How to find Missing Sequences

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Now I want an sql statement that can fetch me a list of all these skipped
> sequence id...ie, from 11 to 15.

SELECT
CASE WHEN start = finish THEN start::text
ELSE start || '-' || finish END AS gap
FROM (
SELECT hole.id AS start, MIN(f.id)-1 AS finish
FROM fred f, (
SELECT id+1 AS id FROM fred f1
WHERE NOT EXISTS (SELECT 1 FROM fred f2 WHERE f2.id = f1.id+1)
) AS hole
WHERE f.id > hole.id
GROUP BY 1
) AS wilma;

Here is the table I used to test with:

CREATE TABLE fred (
id INTEGER PRIMARY KEY
);

INSERT INTO fred VALUES (1);
INSERT INTO fred VALUES (2);
INSERT INTO fred VALUES (3);
INSERT INTO fred VALUES (5);
INSERT INTO fred VALUES (6);
INSERT INTO fred VALUES (7);
INSERT INTO fred VALUES (10);
INSERT INTO fred VALUES (16);
INSERT INTO fred VALUES (18);
INSERT INTO fred VALUES (30);

Of course, if skipped numbers are that important, you may want to use something
other than a sequence...

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200307150953

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/FAhbvJuQZxSWSsgRAgbkAJ9e4UfZ0Q/5tm06tz+TBwRvJ5Z3rACglkjU
Nkus+/x16JBtv1avzJgIEw0=
=u0Hf
-----END PGP SIGNATURE-----