February 3, 2009 at 3:48 pm
I work with contributor data across a nonprofit with many chapters. I want to list the top 10 contributors for each chapter without the dreaded RBAR.
The table tblMemberSummary contains the fields ID,Chapter,Total2008.
Results can be like
1, ChapA, 10000
2, ChapA, 50000
.
.
462, ChapA, 1000
6,ChapB, 9000
55,ChapB, 8000
etc
I'll review and split them by chapter. These aren't the top x * 10 givers overall, but rather the top 10 to each chapter.
I'm tempted to use something like
select distinct chapter into looptable from tblMemberSummary
then loop through the table selecting top 10 for each value of chapter but I understand that's bad form somehow?
What is a better way?
February 3, 2009 at 4:07 pm
Use the RANK function to do a rank within subcategory, and then select only those with a rank of 10 or less.
The RANK function is documented in SQL Server 2008 Books Online.
February 3, 2009 at 9:29 pm
Thank you, that worked quite well. I still needed two steps it appeared, one to rank the whole table and a second to pull the top 10. Here's the SQL I used:
SELECT
RANK() OVER
(PARTITION BY a.organization ORDER BY a.total2008 DESC) AS RANKING
,a.impactid
into tblRanking
FROM tblMemberSummary a
where a.total2008>0
and a.organization is not null
GO
select b.ranking,a.*
from tblMemberSummary a
join tblRanking b
on a.impactid=b.impactid
where b.ranking<11
order by a.organization,b.ranking
go
February 3, 2009 at 9:44 pm
You could do the same in one query by using your first query as a derived table.
select
b.ranking,
a.*
from
tblMemberSummary a
join
(
select
RANK() OVER
(PARTITION BY a.organization ORDER BY a.total2008 DESC) AS RANKING
,a.impactid
from
tblMemberSummary a
where
a.total2008>0 and
a.organization is not null
) b
on a.impactid=b.impactid
where
b.ranking < 11
order by
a.organization,
b.ranking
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply