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

Answer 1

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.