Rank

UDA
Enthusiast

Rank

Hey.

I have a dataset as can be setup below. I want to assign a sequential integer values to a group of related data. However rank doesn't do exactly what I want but is very close. I'm wondering does Teradata have a function to give me what I need.

DDL:

create multiset table MMG_Get_BCP_H_YLISTS (

POLICY_CODE char(20),

TRANS_TYPE CHAR(2),

TRANS_SEQ_NO INTEGER,

LISTS_ID CHAR(3),

UNIT_ID SMALLINT,

LISTS_ATT_ID CHAR(12));

INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 1, 'A', 0, 'abc123def456');

INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 1, 'B', 1, '---abc------');

INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'A', 0, 'ghi789jkl012');

INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'A', 1, 'mno345pqr678');

INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'B', 1, '---def------');

SELECT RANK() Over (order by bcp.LISTS_ID),

bcp.LISTS_ID as List_Name,

CASE WHEN bcp.LISTS_ID = 'A' THEN SUBSTRING(LISTS_ATT_ID,1,12)

WHEN bcp.LISTS_ID = 'B' THEN SUBSTRING(LISTS_ATT_ID,4,3)

WHEN bcp.LISTS_ID = 'C' THEN SUBSTRING(LISTS_ATT_ID,4,3)

ELSE 'Not Defined'

END

FROM MMG_Get_BCP_H_YLISTS bcp

gives me......

1 A ghi789jkl012

1 A abc123def456

1 A mno345pqr678

4 B def

4 B abc

But I want

1 A ghi789jkl012

1 A abc123def456

1 A mno345pqr678

2 B def

2 B abc

Any ideas much appreciated.

I know I can do use an update method but was really just wondering if Teradata has anything that does this automatically.
3 REPLIES
Enthusiast

Re: Rank

Get the Row_Number (NOT Rank) of the unique Lists_Id.
So:

SELECT D1.List_Id_Rank,
bcp.LISTS_ID as List_Name,
CASE WHEN bcp.LISTS_ID = 'A' THEN SUBSTR(LISTS_ATT_ID,1,12)
WHEN bcp.LISTS_ID = 'B' THEN SUBSTR(LISTS_ATT_ID,4,3)
WHEN bcp.LISTS_ID = 'C' THEN SUBSTR(LISTS_ATT_ID,4,3)
ELSE 'Not Defined'
END
FROM MMG_Get_BCP_H_YLISTS bcp
Join (Select ROW_Number() Over (order by D2.LISTS_ID) As List_Id_Rank
, D2.LISTS_ID
From (Select LISTS_ID
From MMG_Get_BCP_H_YLISTS
Group By 1) D2
) D1
On bcp.Lists_Id = D1.Lists_Id
Order By 1,2,3
;

HTH
Senior Apprentice

Re: Rank

You're looking for a DENSE_RANK, there are several ways to calculate it:

http://www.teradata.com/teradataforum/Topic9590-9-1.aspx

Dieter
Enthusiast

Re: Rank

Thanks Guys.