how to assign an incremental ID for duplicate values

[pranaview]

Hi Guys,

Requirement: So, I am looking for a way to uniquely identify a column which has duplicate values. There is no other unique ID column in the table which i can use to create a composite key. I was thinking if I can number the duplicate values then I can I have a unique ID.

for example, something like this:

ID                  AutoIncrementNo.    or    AutoIncrementNo.

12455                    -                                                      -       

12567                   1                                                      -

12567                   2                                                      1

12567                   3                                                      2

12768                    -                                                      -

12789                    1                                                     -

12789                    2                                                     -

I hope i have the requirement clear enough, please let me know if there's any way i can implement this.

Thanks!

Pranav

[Arthur_Fong · Accepted solution]

Try this as your duplicate counts:

if(RowNo()=1,1,if(peek(ID)=ID,peek(Num1)+1,1)) as Num1

[pranaview]

Thanks Arthur!

This is what I was looking for.