PostgreSQL query: write a query to return the maximum of each group of consecutive numbers

Question

Given a set of numbers like "1,2,3,6,7,8,11,12,15,18,19,20", write a query to return the maximum of each group of consecutive numbers.

need to first find which numbers should be in a group, and then find the max in each group.

create table tt (c1 numeric);
insert into tt values
(1),(2),(3),(6),(7),(8),(11),(12),(15),(18),(19),(20);

So the answer would be 3, 8, 12, 15, 20

score 2 · Answer 1 · answered Jun 27 '20 at 20:49

I would rephrase your question as: get numbers whose next value is not in sequence. If so, you can just use window functions:

select *
from (select c1, lead(c1) over(order by c1) lead_c1 from tt) t
where lead_c1 <> c1 + 1

For your sample data, this produces:

c1 | lead_c1
-: | ------:
 3 |       6
 8 |      11
12 |      15
15 |      18

If you want to also capture the very last record (which, obviously, has no follower), you can change the where clause to:

where lead_c1 <> c1 + 1 or lead_c1 is null

score 1 · Accepted Answer · answered Jun 27 '20 at 20:56

I cannot close this question as a duplicate, but I can copy my answer here: Assuming you want 3, 8, 12, 15, and 20, you would use lead():

select c1
from (select t.*, lead(c1) over (order by c1) as next_c1
      from table1 t
     ) t
where next_c1 is distinct from c1 + 1;

This uses the observation that you can find the end number just by comparing the "next number" to the current value plus 1.

If you want these in a string:

select string_agg(c1::text, ',' order by c1)

Here is a db<>fiddle.

2 Answers2