[thelist] MySQL queries, quotes, and AUTO_INCREMENT

[Rob Smith]

Thanks to SQL Server 2005, you can do the new function row_number() to
help solve the Island and Gap problem much more efficiently. The
following is a copy and paste from the lecturer, Itzik Ben-Gan. He's got
some really cool stuff on Recursive Queries and the new CTE (Common
Table Expressions) new to 2005. As a test, I've now mapped out my entire
company's corporate ladder in a matter of a few minutes in a recursive
algorithm....Now on to printing the entire menu navigation as a site
map/catalog application.

http://www.sql.co.il/books/insidetsql2005/source_code/Discover%20the%20P
ower%20of%20Row%20Numbers.txt (Basic - excerpt below)
http://www.sql.co.il/books/insidetsql2005/source_code/Hierarchies%20and%
20Recursive%20Queries.txt (Advanced)

---------------------------------------------------------------------
-- Islands and Gaps
---------------------------------------------------------------------
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.T1 VALUES(1);
INSERT INTO dbo.T1 VALUES(2);
INSERT INTO dbo.T1 VALUES(3);
INSERT INTO dbo.T1 VALUES(100);
INSERT INTO dbo.T1 VALUES(101);
INSERT INTO dbo.T1 VALUES(102);
INSERT INTO dbo.T1 VALUES(103);
INSERT INTO dbo.T1 VALUES(500);
INSERT INTO dbo.T1 VALUES(997);
INSERT INTO dbo.T1 VALUES(998);
INSERT INTO dbo.T1 VALUES(999);
INSERT INTO dbo.T1 VALUES(1000);
GO

-- Gaps
SELECT col1 + 1 AS start_range,
  (SELECT MIN(col1) FROM dbo.T1 AS B
   WHERE B.col1 > A.col1) AS end_range
FROM dbo.T1 AS A
WHERE NOT EXISTS
  (SELECT * FROM dbo.T1 AS B
   WHERE B.col1 = A.col1 + 1)
  AND col1 < (SELECT MAX(col1) FROM dbo.T1);

-- With row numbers
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
      FROM dbo.T1) AS D
GROUP BY grp;

-- Islands
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1,
        (SELECT MIN(col1) FROM dbo.T1 AS B
         WHERE B.col1 >= A.col1
           AND NOT EXISTS
             (SELECT * FROM dbo.T1 AS C
              WHERE C.col1 = B.col1 + 1)) AS grp
      FROM dbo.T1 AS A) AS D
GROUP BY grp;

-- With row numbers
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
      FROM dbo.T1) AS D
GROUP BY grp;
GO

Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236