Re: Top n in group by

> Hi,
>
> I'm looking for a way to get the top n results in each group.
>
> ie. in my database of players:
> Select TeamName, PlayerName, Goals
> From playerList
> where age<20
> group by teamName, PlayerName, Goals
>
> I'm looking for the top n players in each team with the most goals with
 age<20.
>
>
> Thanks for your help.

Hi,

Try this: (SQL has not been tested)

Select TeamName, PlayerName, Goals,

   RANK() OVER (PARTITION BY TeamName ORDER BY Goals DESC) as rank From playerList
where age<20 and rank <=n
group by teamName
order by teamName, rank;

or

Select TeamName, PlayerName, Goals
from
(Select TeamName, PlayerName, Goals,

   RANK() OVER (PARTITION BY TeamName ORDER BY Goals DESC) as rank From playerList
where age<20)
where rank <=n
group by teamName
order by teamName, rank;

Johnny Jiang Received on Sun Mar 28 2004 - 01:41:04 CET