Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2010 @ 09:07:13 GMT


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


Subj:   Excluding Top and Bottom 5 % of records
 
From:   Maca & Dot

Hi all,

I am looking at the most efficient method to exclude the top 5% and bottom 5% of records. To date, the best I can do is

Table: TestTable with fields Region,Team,Player,Results

Object: To exclude the top and bottom 5% for each Team

     SEL
        Region
     ,   Team
     ,   Player
     ,   Results
     ,   Count(*) over (Partition by Team) AS Counter
     ,   Row_Number() over (Partition by Team Order By Results desc) AS Rower
     FROM
     TestTable
     Qualify Rower > Counter * 0.05 AND Rower < Counter * 0.95 ;

Is this the best method or is there some functionality I have missed ? I would have thought there may be some other function that may perform this.

Cheers.... Peter



     
  <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: 27 Dec 2016