Aggregate numbers that are in sequence

Question

I have a table part_tab with column serial_no:

PART_NO     SERIAL_NO
   A            1
   A            2
   A            3
   A            5
   A            7
   A            8
   A            9
   A            10

I would like to aggregate serial_no values in one row when they are in sequence:

PART_NO     SERIAL_NO
   A            1-3
   A            5
   A            7-10

Grouping is by part_no. So for Part No "A" I would like to select the serial_no in one column with values "1-3", "5", "7-10". Selected column should range from min to max and in increasing order.

I edited your question in order to better describe the output. Could you check the modification is in line with your expectations? — trincot, Nov 13 '18 at 15:40

score 1 · Answer 1 · answered Nov 13 '18 at 15:50

That is a gaps & islands problem you can approach by numbering your rows and subtracting those numbers from the serial numbers. This gives you the groups you need.

select
  part_no,
  case when min(serial_no) = max(serial_no)
    then to_char(min(serial_no))
    else min(serial_no) || '-' || max(serial_no)
  end as serial_nos
from
(
  select
    part_no,
    serial_no,
    serial_no - row_number() over (partition by part_no order by serial_no) as grp
  from mytable
)
group by part_no, grp
order by part_no, min(serial_no);

trincot · Accepted Answer · 2018-11-14T14:13:59.117

You could do it without pl/sql, using a query with some common table expressions (with clause). It would look like this:

with add_break as (
    select part_no, 
           serial_no,
           serial_no-1-lag(serial_no,1,0) over (partition by part_no order by serial_no) brk
    from part_tab
),
add_group as (
    select add_break.*, 
           sum(brk) over (partition by part_no order by serial_no) as grp
    from   add_break
)
select part_no,
       case when min(serial_no) = max(serial_no) then to_char(min(serial_no))
            else to_char(min(serial_no)) || '-' || to_char(max(serial_no))
       end range
from   add_group
group by part_no, grp
order by 1, 2

Output for your example data:

part_no | range
--------+------
   A    | 1-3
   A    | 5
   A    | 7-10

Thank you! This is exactly what I mean – slayer22 Nov 14 '18 at 13:55 — slayer22, Nov 14 '18 at 13:55

Aggregate numbers that are in sequence

2 Answers2