Archives of the TeradataForum
Message Posted: Thu, 09 Aug 2007 @ 15:57:31 GMT
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) ;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|