Oracle Row Number Within Sequence of Same Values in Column

Question

So I have this raw data:

TYPE_DESCRIPTION  TYPE_ORDER
PROFILING                  1
RIBBONNING                 2
RIBBONNING                 3
EXTRUSION                  4
SPIRALING                  5
SPIRALING                  6
RIBBONNING                 7
ARMOURING                  8
RIBBONNING                 9
RIBBONNING                10
ARMOURING                 11
RIBBONNING                12
RIBBONNING                13
RIBBONNING                14
RIBBONNING                15
RIBBONNING                16
RIBBONNING                17
RIBBONNING                18
RIBBONNING                19
EXTRUSION                 20

What I want is to get the row number from grouping TYPE_DESCRIPTION, but it has to reset the count when the group changes, considering the TYPE_ORDER field. Basically the result I expect is:

 TYPE_DESCRIPTION  TYPE_ORDER  ROW_NO_BY_GROUP
 PROFILING                  1                1
 RIBBONNING                 2                1
 RIBBONNING                 3                2
 EXTRUSION                  4                1
 SPIRALING                  5                1
 SPIRALING                  6                2
 RIBBONNING                 7                1
 ARMOURING                  8                1
 RIBBONNING                 9                1
 RIBBONNING                10                2
 ARMOURING                 11                1
 RIBBONNING                12                1
 RIBBONNING                13                2
 RIBBONNING                14                3
 RIBBONNING                15                4
 RIBBONNING                16                5
 RIBBONNING                17                6
 RIBBONNING                18                7
 RIBBONNING                19                8
 EXTRUSION                 20                1

Notice that the value RIBBONNING repeats in multiple rows, but the ROW_NUMBER resets each time the TYPE_DESCRIPTION changes.

Thanks..

Alex Poole · Accepted Answer · 2018-09-17 18:51:29Z

3

You can use the Tabibitosan method to form the contiguous groups:

select type_description, type_order,
  row_number() over (order by type_order)
    - row_number() over (partition by type_description order by type_order) as group_no
from your_table;

TYPE_DESCRIPTION TYPE_ORDER   GROUP_NO
---------------- ---------- ----------
PROFILING                 1          0
RIBBONNING                2          1
RIBBONNING                3          1
EXTRUSION                 4          3
SPIRALING                 5          4
SPIRALING                 6          4
RIBBONNING                7          4
ARMOURING                 8          7
RIBBONNING                9          5
RIBBONNING               10          5
ARMOURING                11          9
RIBBONNING               12          6
RIBBONNING               13          6
RIBBONNING               14          6
RIBBONNING               15          6
RIBBONNING               16          6
RIBBONNING               17          6
RIBBONNING               18          6
RIBBONNING               19          6
EXTRUSION                20         18

And then get the row numbers within those groups:

select type_description, type_order,
  row_number() over (partition by type_description, group_no
    order by type_order) as row_no_by_group
from (
  select type_description, type_order,
    row_number() over (order by type_order)
      - row_number() over (partition by type_description order by type_order) group_no
  from your_table
)
order by type_order;

TYPE_DESCRIPTION TYPE_ORDER ROW_NO_BY_GROUP
---------------- ---------- ---------------
PROFILING                 1               1
RIBBONNING                2               1
RIBBONNING                3               2
EXTRUSION                 4               1
SPIRALING                 5               1
SPIRALING                 6               2
RIBBONNING                7               1
ARMOURING                 8               1
RIBBONNING                9               1
RIBBONNING               10               2
ARMOURING                11               1
RIBBONNING               12               1
RIBBONNING               13               2
RIBBONNING               14               3
RIBBONNING               15               4
RIBBONNING               16               5
RIBBONNING               17               6
RIBBONNING               18               7
RIBBONNING               19               8
EXTRUSION                20               1

edited Sep 17, 2018 at 18:51

answered Sep 17, 2018 at 18:36

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Felipe Carlesso Over a year ago

Thanks for the answer, but almost.. line number 7 should be 1 in the final result.

Alex Poole Over a year ago

@FelipeCarlesso - sorry, forgot to partition the outer query. Fixed.

Felipe Carlesso Over a year ago

Awesome, it worked!! Thanks, I was trying to do that for 4 hours already...

Collectives™ on Stack Overflow

Oracle Row Number Within Sequence of Same Values in Column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related