Postgresql Given "1,2,3,6,7,8,11,12,15,18,19,20", A Query To Return The Maximum Of Each Group Of Consecutive Numbers

given 1,2,3,6,7,8,11,12,15,18,19,20, write a query to return the maximum of each group of the consecutive numbers are grouped by the query below, but I don't know how to obtain the

Solution 1:

Assuming you want 3, 8, 12, 15, and 20, you would use lead():

select c1
from (select t.*, lead(c1) over (orderby c1) as next_c1
      from table1 t
     ) t
where next_c1 isdistinctfrom 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:

selectstring_agg(c1::text, ',' order by c1)

Here is a db<>fiddle.