Selecting Top N Results - SQL Server Forums

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

Selecting Top N Results

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-10-11 : 14:03:56

Hi,

I have a requirement wherein I need to select the top 10 managers from each shop based on the total no of calls serviced by them .

The sql query that I have gives me the all the managers and their total calls for all the shops. I would need top 10 managers from each shop along with their no of calls serviced.

Below is the code

SELECT
Shop_Code,
Shop_Name,
Manager_Name_And_Code,
sum(Number_of_Total_Calls_Serviced) as Number_of_Total_Calls
FROM
dbo.Facilities
GROUP BY
Shop_Code,
Shop_Name,
Manager_Name_And_Code
ORDER BY
Shop_Code asc,
Shop_Name asc,
Number_of_Total_Calls desc,
Manager_Name_And_Code asc

Please advice about the possible way to get the top 10 managers from each shop based on the no of calls they serviced.

Thank You,

Dp

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-11 : 14:32:10

Here's one way:


SELECT f.Shop_Code,
       f.Shop_Name,
       ca.Manager_Name_And_Code,
       max(ca.Number_of_Total_Calls) Number_of_Total_Calls --min/max doesn't matter cause there'll just be one/group
FROM   dbo.Facilities f
cross apply (
       select top 10 
              x.Manager_Name_And_Code
              ,sum(x.Number_of_Total_Calls_Serviced) as Number_of_Total_Calls
       from   dbo.Facilities x
       where  x.Shop_Code = f.Shop_Code
       and    x.Shop_name = f.Shop_name
       group by x.Manager_Name_And_Code
       order by sum(x.Number_of_Total_Calls_Serviced) desc 
       ) ca
GROUP BY f.Shop_Code,
       f.Shop_Name,
       ca.Manager_Name_And_Code
ORDER BY f.Shop_Code asc,
       f.Shop_Name asc,
       ca.Number_of_Total_Calls desc,
       ca.Manager_Name_And_Code asc

Be One with the Optimizer
TG

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 04:16:36

Another way


select * from(

	select *,row_number()over(partition by Shop_Code,
	Shop_Name,
	Manager_Name_And_Code order by Number_of_Total_Calls desc )rid
	from
	(
		SELECT
		distinct Shop_Code,
		Shop_Name,
		Manager_Name_And_Code,
		sum(Number_of_Total_Calls_Serviced)over(partition by Shop_Code,Shop_Name,Manager_Name_And_Code) as Number_of_Total_Calls
		FROM
		dbo.Facilities
	)T
)T1 where rid<=10

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-12 : 04:30:33

Note that both of those solutions are only available on sql server 2005 or above. (which you are most likely on)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dim
Yak Posting Veteran

57 Posts

Posted - 2010-10-12 : 11:20:20

Yes. Thank You for the help Transact Charlie and Sachin.Nand Both the cosde works !.

Dp

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 11:22:57