Stories
Slash Boxes
Comments
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

use Perl Log In

Log In

[ Create a new account ]

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
More | Login | Reply
Loading... please wait.
  • Cool!

    I think I must review what the "CROSS JOIN" does in my SQL manual... :-)
    Thanks for sharing.
    • 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