Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 24 Aug 2004 @ 20:24:20 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Rank() and Partition By
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, August 24, 2004 16:00 -->

I'm trying to get a result set ranking the top performers measured by actualhrs turned in by people per month. The following SQL partitions correctly, resetting the rank as the yearmonth changes, but returns rows for every person. I've tried adding

     QUALIFY RANK(yearmonth ASC, actualhrs DESC) <=100;

but that only returns the top 100 in the 1st yearmonth. How can I restrict the query to return n rows per yearmonth, or the people whose rank is <= 10?

     SELECT a.id, fullname, yearmonth, actualhrs,
     RANK() OVER (PARTITION BY yearmonth
     ORDER BY actualhrs DESC)
     FROM resource_time a join people_info b on a.id = b.id


  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020