Search code examples
sqlsql-serverpostgresqlrow-number

SQL - How to Label Number for Each ROW & BLOCK

I have a table column like this

A, A, B, C, A, A, B, D, E, E, E

I Would like to label number for each ROW & block like this 

(A, 1), (A, 1), (B, 2), (C, 3), (A, 4), (A, 4), (B, 5), (D, 6), (E, 7), (E, 7), (E, 7)

How to do? Thank you.

Solution

  • Assuming you have a table like this:

    SELECT * FROM t ORDER BY ord

let, ord
--------
A, 1
A, 2
B, 3
C, 4
A, 5
A, 6
B, 7
D, 8
E, 9
E, 10
E, 11

    If you do this:

    with cte as(
  select let, ord, case when lag(let) over(order by ord) <> let then 1 else 0 end as letchanged
  from yourtable
)

select let,
  1 + sum(letchanged) over(order by ord rows unbounded preceding) as ctr
from cte

    Then you will get:

    let, ctr
--------
A, 1
A, 1
B, 2
C, 3
A, 4
A, 4
B, 5
D, 6
E, 7
E, 7
E, 7