Archives of the TeradataForum

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

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

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>>