All Forums Database
akyz 5 posts Joined 05/14
30 May 2014
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!
 

-AK
Santanu84 122 posts Joined 04/13
01 Jun 2014

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

akyz 5 posts Joined 05/14
02 Jun 2014

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

-AK

Santanu84 122 posts Joined 04/13
03 Jun 2014

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

You must sign in to leave a comment.