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

Question

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 maximum for each group of consecutive numbers with my current query

with trans as (
  select c1, 
         case when lag(c1) over (order by c1) = c1 - 1 then 0 else 1 end as new
    from table1
), groups as (
  select c1, sum(new) over (order by c1) as grpnum
    from trans
), ranges as (
  select grpnum, min(c1) as low, max(c1) as high
    from groups
   group by grpnum
), texts as (
  select grpnum, 
         case 
           when low = high then low::text 
           else low::text||'-'||high::text
         end as txt
    from ranges
)
select string_agg(txt, ',' order by grpnum) as number
  from texts;

Please clarify...`ranges.high` _is_ "the maximum for each group of consecutive numbers" — Bill Jetzer, Jun 27 '20 at 10:26
Yes, `ranges.high` is the maximum per group. You already have it. What else do you need? — The Impaler, Jun 27 '20 at 11:27

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

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.

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

1 Answers1

Linked

Related