Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Minimum values to represent the source set query
 
From:   Dmitriy.Boyko

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023