Getting Top 25 records for each group

[Lftsk]

I have a table (tblAccts). It has 3 columns (AcctNo, Category, Balance). There are 4 possible choices (A, B,
C, D) for the category column.

I need to get the top 25 rows (based on the dollar amount in the Balance column) in each category. I don't want them grouped together. I just need my result set to show the top 25 rows sorted in descending order within each category.

Any help would be greatly appreciated.

 

[MeanGreen]

I am sure there is a better way, but a quick solution would be:

select top 25 category,balance from tblaccts
where category = 'A'
UNION
select top 25 category,balance from tblaccts
where category = 'B'
UNION
select top 25 category,balance from tblaccts
where category = 'C'
UNION
select top 25 category,balance from tblaccts
where category = 'D'
ORDER BY CATEGORY,BALANCE

Hope this helps.

 

[Lftsk]

Thanks,

that would work execept for example's sake I said only 4 categories. I actually have over 140. So manually entering each category would be somewhat cumbersome.

Thanks again.

 

[MeanGreen]

You could try this:

select category,balance from (
Select category, balance, recNo=
(Select count(*)
From tblaccts
Where category = a.category and balance >= a.balance)
From tblaccts a
) x
where x.recno < 26
Order By category,balance desc, RecNo

Hope this helps.

 

[tlbroadbent]

Try this solution. If there are ties in the TOP 25 balance amounts, it could return more than 25 rows per category.

Select Acctno, Balance, Category
From tblAccts ta
Where AcctNo In
(Select Top 25 AcctNo
From tblAccts
Where Category=ta.Category
Order By Balance Desc)
Order By Category, Balance Desc If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm