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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy