Archives of the TeradataForum

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

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

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

Thanks

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