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@turnstep.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-----