Archives of the TeradataForum

Message Posted: Sat, 21 Aug 2010 @ 08:07:05 GMT

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

Hi! Here is the solution. You can try to optimize it to get better performance due to your data.

     --Creating the table
     CREATE TABLE Ter_Forum
     (
        RPTID INT,
        BM VARCHAR(10)
     )
     PRIMARY INDEX (RPTID, BM);
     --Filling table with data
     DEL Ter_Forum;
     INSERT INTO Ter_Forum VALUES(121, 'A');
     INSERT INTO Ter_Forum VALUES(121, 'B');
     INSERT INTO Ter_Forum VALUES(122, 'C');
     INSERT INTO Ter_Forum VALUES(122, 'D');
     INSERT INTO Ter_Forum VALUES(123, 'B');
     INSERT INTO Ter_Forum VALUES(123, 'D');
     INSERT INTO Ter_Forum VALUES(124, 'A');
     INSERT INTO Ter_Forum VALUES(124, 'B');
     INSERT INTO Ter_Forum VALUES(125, 'C');
     INSERT INTO Ter_Forum VALUES(126, 'A');
     INSERT INTO Ter_Forum VALUES(126, 'C');
     INSERT INTO Ter_Forum VALUES(126, 'D');
     --Creating view, because Teradata doesn't support subqueries in recursive
     query
     CREATE VIEW v_ter_forum
     AS
     SELECT bm FROM ter_forum
     GROUP BY 1
     --Result query
     WITH RECURSIVE cte(bm, cbm) AS
     (
     SELECT bm, bm cbm FROM v_ter_forum
     UNION ALL
     SELECT x.bm || ',' || y.bm, y.bm cbm
     FROM
     cte x, v_ter_forum y
     WHERE x.cbm < y.bm
     )
     SELECT bm
     FROM (
     SELECT TOP 1 WITH TIES cte.bm, COUNT(DISTINCT tbl.bm) bm_qty, COUNT(DISTINCT
     rptid) rp_qty
     FROM cte, ter_forum tbl
     WHERE POSITION(',' || tbl.bm || ',' IN ',' || cte.bm || ',') > 0
     GROUP BY 1
     ORDER BY 3 DESC, 2) mT

Best regards,

Dmitriy Boyko

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