Exemplo: Top N usando agrupamento

select
  HRRanks.*
from
(
    Select
      b.yearID, b.PlayerID, sum(b.Hr) as TotalHR,
      rank() over (partition by b.yearID order by sum(b.hr) desc) as HR_Rank
    from
      Batting b
    where
      b.yearID > 1990
    group by
      b.yearID, b.playerID
)
  HRRanks
where
  HRRanks.HR_Rank <= 10

Fonte: http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx