Thursday, March 1, 2007

Find gaps

Well ,there are many questions in the newgroup I have seen with this request
You can have a table with sequential numbers and LEFT join with your table to find the gaps.
The following techniques I collected from Steve Kass,Joe Celko, Itzik Ben-Gan.

CREATE TABLE X (C INT NOT NULL PRIMARY KEY)
INSERT X SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 10

SELECT * FROM x
SELECT
c+n FROM x, (
SELECT 0 n UNION ALL SELECT 1
) T
GROUP BY c+n
HAVING MIN(n) = 1 and c+n < (SELECT MAX(c) FROM x)
-----------------------------------

CREATE TABLE Foobar
(seq INTEGER NOT NULL PRIMARY KEY
CHECK(seq > 0));
INSERT INTO Foobar VALUES (3);
INSERT INTO Foobar VALUES (7);
INSERT INTO Foobar VALUES (8);
INSERT INTO Foobar VALUES (10);
INSERT INTO Foobar VALUES (11);


SELECT (F1.seq +1) AS start, (F2.seq -1) AS fini
FROM (SELECT seq FROM Foobar
UNION ALL
SELECT 0) AS F1,
Foobar AS F2
WHERE (F1.seq +1) BETWEEN 0 AND (F2.seq-1)
AND NOT EXISTs
(SELECT *
FROM Foobar AS F3
WHERE F3.seq BETWEEN (F1.seq+1) AND (F2.seq-1));
-----------------------
SELECT n+1 as startgap, nextn-1 as endgap
FROM(SELECT n,
(SELECT MIN(n)
FROM t1 as b
WHERE b.n > a.n) as nextn
FROM t1 as a) as d
WHERE nextn - n > 1
----------------------------------
SELECT
MIN(i) as low,
MAX(i) as high
FROM(
SELECT
N1.num,
COUNT(N2.num) - N1.num
FROM Numbers AS N1, Numbers AS N2
WHERE N2.num <= N1.num
GROUP BY N1.num
) AS N(i,gp)
GROUP BY gp

1 comment:

aiya said...
This comment has been removed by a blog administrator.