Use RANK (or other funtion) to number duplicate values in a field, start over with each new field value

Database
Enthusiast

Use RANK (or other funtion) to number duplicate values in a field, start over with each new field value

Greetings,

I am attempting to rank entires in a table, but want the rank to start over with each new value in the ID field.  The idea is to create a unique identifier out of the NAME and the ranking which is based on the number of lines of each ID value.  See example below...

Here is the source data:

ID NAME
1 Joe
1 Joe
1 Joe
2 John
5 Jim
5 Jim
7 Jared
7 Jared
7 Jared

Here is the desired output:

ID NAME NAME_RANK
1 Joe Joe-001
1 Joe Joe-002
1 Joe Joe-003
2 John John-001
5 Jim Jim-001
5 Jim Jim-002
7 Jared Jared-001
7 Jared Jared-002
7 Jared Jared-003

I am thinking this will involve a ranking and concatenation, but I am stuck.  Any help is greatly appreciated!

Tags (2)
3 REPLIES
Enthusiast

Re: Use RANK (or other funtion) to number duplicate values in a field, start over with each new field value

Hi

Try the below query, hope this will work.

SEL ID, NAME , NAME||'-00'||TRIM(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS NAME_RANK

FROM DB_NAME.TABLE_NAME

ORDER BY ID, NAME_RANK

;

Santanu

Enthusiast

Re: Use RANK (or other funtion) to number duplicate values in a field, start over with each new field value

Thank you, Santanu!

The code worked, except I have cases where there are more than 9 records with a given ID, so I updated the code for the new field to look like this in order to maintain the 3-character/leading zero pad on the "-001, -002, ... -013" suffixes:

, NAME ||'-'|| TRIM( CAST (CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS FORMAT'-9(3)') AS CHAR(3))) AS NAME_RANK

Works like a charm

Enthusiast

Re: Use RANK (or other funtion) to number duplicate values in a field, start over with each new field value

Hi

I gave my suggestion based on the sample data above. It is good to hear that it has worked.

Thanks

Santanu