#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    6
    Rep Power
    0

    Extract continuous ranges from series


    I have a table like this one:
    Code:
    CREATE TABLE series (
      x INTEGER NOT NULL,
      CONSTRAINT pk PRIMARY KEY (x)
    );
    
    INSERT INTO series (x) VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (8),
    (11),
    (12),
    (13),
    (14),
    (18),
    (19),
    (21),
    (22),
    (23),
    (24);
    I can retrieve either the first or the last value in a continuous range with these queries:
    Code:
    SELECT S1.x AS x1
    FROM series S1 
    WHERE NOT EXISTS (SELECT * FROM series S2 WHERE S1.x=S2.x+1);
    
    SELECT S1.x AS x2
    FROM series S1 
    WHERE NOT EXISTS (SELECT * FROM series S2 WHERE S1.x=S2.x-1);
    However, I'd like to have one query to return the two columns x1 and x2 side-by-side in one row like this:
    Code:
    x1 | x2
    --------
     1 |  5
     8 |  8
    11 | 14
    18 | 19
    21 | 24
    Thanks for any suggestions!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT a.x Start
         , MIN(c.x) End 
      FROM series a
      LEFT 
      JOIN series b 
        ON a.x = b.x + 1 
      LEFT 
      JOIN series c 
        ON a.x <= c.x
      LEFT 
      JOIN series d 
        ON c.x = d.x - 1
     WHERE b.x IS NULL 
       AND c.x IS NOT NULL
       AND d.x IS NULL 
     GROUP 
        BY a.x;
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    6
    Rep Power
    0
    I like that -- thanks!

    (Now if I can figure out WHY this works...)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    It may be instructive to consider this intermediate result...
    Code:
    SELECT a.x ax
         , b.x bx
         , c.x cx
         , d.x dx
      FROM series a
      LEFT 
      JOIN series b 
        ON a.x = b.x + 1 
      LEFT 
      JOIN series c 
        ON a.x <= c.x
      LEFT 
      JOIN series d 
        ON c.x = d.x - 1
     ORDER 
        BY a.x
         , b.x
         , c.x
         , d.x;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    6
    Rep Power
    0
    Yes, that helps. Very clever!

IMN logo majestic logo threadwatch logo seochat tools logo