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:

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:

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

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.