How can i select top n rows from grouped results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can i select top n rows from grouped results

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2011-10-23 : 20:56:11

Sorry about the vague title but i couldn't found any suitable title her is a scenario:

i have a expenses table and a categories table like that:


Categories
(
   CategoryId
   CategoryName
)
Expenses
(
   ExpensesId
   Spends decimal
   CategoryId
)

i want to select top two Spends for each Category

if i had


  Id  Spends  CategoryId
   1     20       1
   2     10       1
   3     60       1
   4     90       2
   5     10       2
   6     16       2

this should returns


   Spends  CategoryId
     60        1
     20        1
     90        2
     16        2

i also want to join the categories table to select the category name
i tried something like the following you can modify it


select cc.cn, cc.ss from
(
    select  c.Category_Name cn, e.Spends ss from Expenses e 
    inner join Categories c on e.Category_Id = c.Category_Id 
    group by  e.Spends,c.Category_Name
 ) as cc
 order by cc.ss

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 21:13:47

[code]
select cc.cn, cc.ss from
(
select c.Category_Name cn, e.Spends ss, row_number() over (partition by c.Category_Name order by e.Spends desc) rn
from Expenses e
inner join Categories c on e.Category_Id = c.Category_Id
) as cc
where rn <= 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]