### Thread: Extract continuous ranges from series

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. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,953
Rep Power
382
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;```
3. 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...)
4. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,953
Rep Power
382
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;```
5. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

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