Archives of the TeradataForum

Message Posted: Thu, 09 Aug 2007 @ 15:57:31 GMT

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

As mentioned by Mike try using a qualify to eliminate additional data. You will notice high spool issue with these OLAP. The other approach is you can rewrite this to use the Teradata Rank using 'group by' and see if it helps.

In your case you can try:

     Select activity_prd_id ,
        site_id,
        user_id,
        activity_count ,
        rank( activity_count  )  As  activity_count_rank
     >From        tb_test
     Group        By   activity_prd_id , site_id
     QUALIFY rank( activity_count  ) <= (Some condition) ;

The solution is to use : Row_Num which is ANSII like the Ansii Rank. However the Row_Number does not return duplicate value like tie.

So this should work:

     Select activity_prd_id ,
        site_id,
        user_id,
        activity_count ,
        ROW_NUMBER () over (Partition By  activity_prd_id , site_id
     Order        By activity_count Desc ) activity_count_rank
     >From        tb_test
     QUALIFY        rank( activity_count  ) <= (Some condition) ;

Good luck!

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