![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Thu, 14 Jan 2010 @ 10:15:50 GMT
Subj: | | Re: Need SQL help: rollup on column |
|
From: | | Anderson, Dirk |
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
| |