Archives of the TeradataForum

Message Posted: Thu, 14 Jan 2010 @ 10:15:50 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

     CREATE VOLATILE TABLE vt
     (X CHAR(14) NOT NULL
     ,Y INTEGER  NOT NULL
     ,Z BYTEINT  NOT NULL
     ,P2 CHAR(1) NOT NULL
     )
     PRIMARY INDEX(x,y,z)
     ON COMMIT PRESERVE ROWS
     ;

     INS INTO vt VALUES('01041708773046',96496, 0,'A'); INS INTO vt
     VALUES('01041708773046',96496, 1,'B'); INS INTO vt VALUES('01041708773046',96496, 1,'C');
     INS INTO vt VALUES('01041708773046',96496, 1,'D'); INS INTO vt
     VALUES('01041708773046',96496, 2,'E'); INS INTO vt VALUES('01041708773046',96496, 2,'F');
     INS INTO vt VALUES('01041708773046',96496, 2,'G'); INS INTO vt
     VALUES('01041708773046',96496, 3,'H');

     /* Add ranking to the table */

     CREATE VOLATILE TABLE vt_rnk
     (X       CHAR(14) NOT NULL
     ,Y       INTEGER  NOT NULL
     ,Z       BYTEINT  NOT NULL
     ,P2      CHAR(1)  NOT NULL
     ,RNK     INTEGER  NOT NULL
     ,MAX_RNK CHAR(1)  NOT NULL
     )
     PRIMARY INDEX(x,y,z)
     ON COMMIT PRESERVE ROWS
     ;
     DELETE FROM vt_rnk;
     INSERT INTO vt_rnk
     SELECT x,y,z,p2,Rnk,CASE WHEN Rnk=Max_Rnk THEN 'Y' ELSE 'N' END FROM ( SELECT
     x,y,z,p2,RANK() OVER (PARTITION BY x,y,z ORDER BY x,y,z,p2) AS Rnk
     ,SUM(1) OVER (PARTITION BY x,y,z) MAX_Rnk FROM vt
     ) dt
     ;

     WITH RECURSIVE recur
        (x, y, z, p2, rnk, max_rnk)
     AS (
          SEL x,y,z,p2 (VARCHAR(30)),Rnk,Max_Rnk
          FROM vt_rnk
          WHERE RNK = 1
          UNION ALL
          SEL curr.x,curr.y,curr.z,recur.p2||curr.p2,curr.rnk,curr.max_rnk
          FROM vt_rnk curr
          INNER JOIN recur
             ON curr.x=recur.x
            AND curr.y=recur.y
            AND curr.z=recur.z
            AND curr.rnk=recur.rnk+1
        )
     SEL x,y,z,p2
     FROM recur
     WHERE Max_Rnk='Y'
     ORDER BY 1,2,3
     ;

Thanks.

Dirk Anderson
Architect
Enterprise Information Management

	<Prev	Next>		<<First	<Prev	Next>	Last>>