SQL query to extract ranges from table of integers

Question

This may have been answered here before, but I have no clue how to search for it, so my apologies if it's a duplicate. I have a table with integer column, which holds number in sequence.

Some of numbers is missing due to removing of rows:

+-------+
| _auto |
+-------+
|   0   |
|   1   |
|   2   |
|  5    |
|  6    |
|  7    |
|   9   |
|  11   |
|  12   |
|  13   |
|  14   |
|   16  |
|   17  |
|  19   |
|  20   |
+-------+

What I want, is to write SQL query which gave me a table consists of ranges on this column.

+----+----+
|  F |  T |
+----+----+
|  0 |  2 |
|  5 |  7 |
|  9 |  9 |
| 11 | 14 |
| 16 | 17 |
| 19 | 20 |
+----+----+

I'll be using it with QT4::QSqlDatabase driver, so it would be nice if it's work on any compatible database (ANSI SQL), not only PostgreSQL.

I believe in this algorithm: (1) select rows, such that there is no rows with values _auto+1; (2) the same, but _auto-1; (3) union this two selects.

But I have no understanding how to write first two selects.

Answer 1

Sequences of numbers have a property: if you subtract an sequence from them, then the result is a constant. The following query uses this observation:

select min(_auto) as f, max(_auto) as t
from (select n._auto, row_number() over (order by n._auto) as seqnum
      from numbers n
     ) n
group by (_auto - seqnum);