how to add a unique identifier to repeat values in a sequence

Database
Enthusiast

how to add a unique identifier to repeat values in a sequence

Hi All,

 

This seems like a simple problem but I've been scratching my head for a while now - your expert help is much appreciated!

 

I have a data set that follows a prescribed sequence (i.e. I can't change the sequence) - I need to identify each unique instance in the sequence, eg

 

Data Sequence:

A, B, C, D, A, F, G, B

 

Desired Result:

A1, B1, C1, D1, A2, F1, G1, B2

 

I've been playing around with OLAP functions but can't quite seem to get the desired result - you help is greatly appreciated!

 

Thanks.

1 REPLY
Junior Contributor

Re: how to add a unique identifier to repeat values in a sequence

Assuming that there's a way to order the data to get this sequence:

data || trim(row_number() over (partition by data order by myordercolumn))