# Group rows by an incrementing column in PostgreSQL

I have the table shown below, with only one column. What I want to achieve is to separate all rows that have no gap in x, for example the numbers 1-3, 5-6 and 8-9 (because the gaps are 4 and 7).

```
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
| 9 |
+---+
```

I would like to make it look like this: a table with two columns (a and b), indicating the ranges where there are no gaps in the previous column x. For every gap a new record is inserted. How would I go about it in PostgreSQL?

```
+---+---+
| a | b |
+---+---+
| 1 | 3 |
| 5 | 6 |
| 8 | 9 |
+---+---+
```

dnoeth
answered question

### 1 Answer

You can compare the sequence with gaps to a sequence without gaps:

```
select min(x), max(x)
from
(
select x,
x-row_number() over (order by x) as dummy
from tab
) as dt
group by dummy
x | row_number | x - row_number
| 1 | 1 | 0 -- same value for consecutive values without gaps
| 2 | 2 | 0
| 3 | 3 | 0
| 5 | 4 | 1
| 6 | 5 | 1
| 8 | 6 | 2
| 9 | 7 | 2
```

dnoeth
posted this

## Have an answer?

JD