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

4 comments:

haiyan said...

Two years passed, I still remembered the scene of fighting against wolves in Tir Chonaill with friends, sharing fruit which was got by our mabinogi gold around a fire, fishing in a clean pond. Players will have five seconds freezed to make their own preparation, when the fight beings, win or lose, it depends on your own cheap mabinogi. Here is some mabinogi money I took months ago; I was the guy wielding a thick sword. And I know it is rude enough to beat a woman. But, we can use some mabinogi online gold to edit some dates in the setup menu to change the clothes we wear. As a kind of fantastic online game, I think Mabinogi is more similar to comic and need to buy mabinogi gold.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

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