NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

## All the Perl that's Practical to Extract and Report

#### Aristotle (5147)

Aristotle
pagaltzis@gmx.de
http://plasmasturm.org/

Blah blah blah blah blah [technorati.com]

### Journal of Aristotle (5147)

Wednesday March 28, 2007
09:37 AM

### Fun with SQL: integer runs

[ #32818 ]

Someone posted an SQL question in a forum I occasionally frequent. The setup:

CREATE TABLE tbl (num INTEGER);
INSERT INTO tbl VALUES (  1);
INSERT INTO tbl VALUES (  2);
INSERT INTO tbl VALUES (  3);
INSERT INTO tbl VALUES (  4);
INSERT INTO tbl VALUES (  9);
INSERT INTO tbl VALUES ( 10);
INSERT INTO tbl VALUES ( 11);
INSERT INTO tbl VALUES ( 20);
INSERT INTO tbl VALUES ( 21);
INSERT INTO tbl VALUES ( 22);
INSERT INTO tbl VALUES ( 23);
INSERT INTO tbl VALUES (100);
INSERT INTO tbl VALUES (101);
INSERT INTO tbl VALUES (102);
INSERT INTO tbl VALUES (103);

His question: is there a way to produce the following output?

start       end
----------  ----------
1           4
9           11
20          23
100         103

I didn’t think it was possible at first, but after some thinking and tinkering, I managed to come up with a solution:

SELECT s.num AS start, MIN( e.num ) AS end
FROM (
SELECT num
FROM tbl t1
WHERE NOT EXISTS ( SELECT NULL FROM tbl t2 WHERE t2.num = t1.num - 1 )
ORDER BY num
) s
CROSS JOIN (
SELECT num
FROM tbl t1
WHERE NOT EXISTS ( SELECT NULL FROM tbl t2 WHERE t2.num = t1.num + 1 )
ORDER BY num
) e
WHERE s.num <= e.num
GROUP BY s.num;

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Full
Abbreviated
Hidden
• #### Cross JOIN(Score:1)

Cool!

I think I must review what the "CROSS JOIN" does in my SQL manual... :-)
Thanks for sharing.
• #### Re:(Score:1)

`CROSS JOIN` is simply an explicit way to request the standard cartesian product.

sqlite> CREATE TABLE tbl (num INTEGER);
sqlite> INSERT INTO tbl VALUES (  1);
sqlite> INSERT INTO tbl VALUES (  2);
sqlite> INSERT INTO tbl VALUES (  3);
sqlite> SELECT t1.num A, t2.num B FROM tbl t1 CROSS JOIN tbl t2;
A           B
----------  ----------
1           1
1           2
1