Extract continuous ranges from series

Dev Shed Forums Sponsor:

September 16th, 2012, 09:47 AM

bobhairgrove

Registered User

Join Date: Sep 2012

Posts: 6

Time spent in forums: 1 h 28 m 40 sec
Reputation 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!

September 17th, 2012, 04:16 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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;

September 17th, 2012, 05:09 AM

bobhairgrove

Registered User

Join Date: Sep 2012

Posts: 6

Time spent in forums: 1 h 28 m 40 sec
Reputation Power: 0

I like that -- thanks!

(Now if I can figure out WHY this works...)

September 17th, 2012, 06:08 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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;

September 17th, 2012, 07:39 AM

bobhairgrove

Registered User

Join Date: Sep 2012

Posts: 6

Time spent in forums: 1 h 28 m 40 sec
Reputation Power: 0

Yes, that helps. Very clever!

Extract continuous ranges from series

Developer Shed Advertisers and Affiliates