Deckard Deckard - 1 month ago 7
SQL Question

Increasing counter of duplicated rows (Not by PL/SQL)

say

select IDX, SUB_IDX, NAME from MYTABLE order by IDX, SUB_IDX
results :

IDX SUB_IDX NAME
1 20 aaa
2 22 bbb
3 22 ccc
4 23 ddd
5 23 eee
6 23 fff


IDX
is PK and I want to count of duplicated of consecutive
SUB_IDX
like this :

IDX SUB_IDX count NAME
1 20 1 aaa
2 22 1 bbb
3 22 2 ccc
4 23 1 ddd
5 23 2 eee
6 23 3 fff


How can I achieve this?

Answer

I want to count of duplicated of consecutive SUB_IDX

Because of your consecutive requirement, the query gets more complicated. Your sample data is a bit too simplistic and doesn't clearly show what your expected output would be when non-consecutive duplicate SUB_IDX values occur.

Still, assuming I am understanding correctly, combining some analytic functions, it is possible. Perhaps someone can find a way to simplify the query:

with grp_starts as (
  select idx, sub_idx, name,
         case when sub_idx = lag(sub_idx) over (order by idx)
           then 0 else 1 end as is_grp_start
  from mytable),
grps as (
  select idx, sub_idx, name,
         sum(is_grp_start) over (order by idx) as grp_id
    from grp_starts)
select idx, 
       sub_idx,
       row_number() over (partition by grp_id order by idx) as count,
       name
  from grps