All Forums UDA
marcmc 112 posts Joined 12/05
23 Jul 2009
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' ENDFROM MMG_Get_BCP_H_YLISTS bcpgives me......1 A ghi789jkl0121 A abc123def4561 A mno345pqr6784 B def4 B abcBut I want1 A ghi789jkl0121 A abc123def4561 A mno345pqr6782 B def2 B abcAny 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.

Jimm 298 posts Joined 09/07
23 Jul 2009

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'ENDFROM MMG_Get_BCP_H_YLISTS bcpJoin (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 ) D1On bcp.Lists_Id = D1.Lists_Id Order By 1,2,3 ;HTH

dnoeth 4628 posts Joined 11/04
23 Jul 2009

You're looking for a DENSE_RANK, there are several ways to calculate it:http://www.teradata.com/teradataforum/Topic9590-9-1.aspxDieter

Dieter

marcmc 112 posts Joined 12/05
24 Jul 2009

Thanks Guys.

You must sign in to leave a comment.