Home > other >  Oracle SQL – counter for consecutive equivalent values under specific column
Oracle SQL – counter for consecutive equivalent values under specific column

Time:08-13

I'm trying to create a counter column for an SQL query, which will start with 1 and increase with each consecutive row with consecutive equivalent values, under a specific column, without re-ordering the query output.

For example, for an SQL query which returns that dummy output:
enter image description here

I would like to "add" an extra column (C), which will be populated with a "new" counter for every encountered value in column A — starting with 1 and going upwards — without re-ordering the query output:
enter image description here

If it helps, I can easily add a running "row id" in the inner SQL - so there's a column that will represent the "correct" order of things.
I've tried to use row_number() for that but it seems to re-order the data.

CodePudding user response:

Without an order-by clause you're at the whim of the optimiser and how it chooses to return the unordered data - which can vary over time, or even between runs of the same query.

With your sample data and the db<>fiddle version and set-up and a fair wind, you can do this with a variation on Tabibitosan:

select a, b,
  row_number() over (partition by a, a_grp order by rn1) as c
from (
  select a, b, rn1, rn1 - row_number() over (partition by a order by rn1) as a_grp
  from (
    select a, b,
      row_number() over (order by null) as rn1
    from your_table
  )
)
order by rn1
A B C
Data1 33 1
Data1 42 2
Data1 13 3
Data33 56 1
Data33 311 2
Data1 54 1
Data2 123 1
Data1 555 1

The supposed ordering is captured by the rn1 alias in the innermost subquery - but again, this isn't guaranteed to give you the same order you get now from a simple select with no ordering. That is then used as part of the calculation and ordering in the outer queries.

db<>fiddle showing the intermediate steps.

If you have a more robust way to generate that innermost rn1 value - maybe something you can extract within the existing query that you're trying to add this column to - then it would be more robust, and you could probably then remove a level of subquery.

You could also then probably use match_recognize as an alternative, or other gaps-and-islands techniques.

  • Related