February 8, 2010 at 4:56 pm
The following is t-sql 2005. What I am doing is selecting the top 5 records in each group by ranking order. What I want is a count of the records selected by region but I am not certain how to accomplish this in sql server 2005. Can you tell me what I could do to modify this code?
SELECT Region,grp1,gnum,cnt, RowNum
FROM
(
SELECT Region,grp1,gnum,cnt, row_number() over (partition by grp1, gnum order by Cnt Desc) RowNum
from (Region,grp1,gnum,count(*) as Cnt
from dbo.tbl1
group by Region,grp1,gnum)RDM
)B
WHERE
RowNum <= 5
ORDER BY 1,2,3,4
thanks!
February 9, 2010 at 12:32 am
I am sure I am getting this requirement all wrong, thats because you have not posted the query properly. have a look at this,
http://www.sqlservercentral.com/articles/Best+Practices/61537/
But if you are asking how to get the count for each region from the inner dataset, then do this?
SELECT Region, count(*)
from (SELECT Region,grp1,gnum,count(*) as Cnt
from dbo.tbl1
group by Region,grp1,gnum)RDM
)B
Group by Region
---------------------------------------------------------------------------------
February 9, 2010 at 9:36 am
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply