SQL Server - Top Saleperson Per Region

Question

SELECT region, person, sum(dollars) as thousands FROM sales GROUP BY region, person ORDER BY region, sum(dollars) desc 

The SQL above provides a complete list of sellers for each region, for example

  region person thousands canada mike smith $114 canada joe blog $76 canada pete dodd $45 usa john doe $253 usa jane smyth $120 europe pieter tsu $546 europ mike lee $520 

If I’m only interested in showing the top seller in each region (as shown below), how can I do this?

  region person thousands canada mike smith $114 usa john doe $253 europe pieter tsu $546 

Answer 1

I did something like Burnall. I didn’t really like the “top 1 with ties” part, so I did it all with a subquery and selected the rows where rank = 1.

 select * from ( select region, person, rank() over(partition by region order by sum(dollars) desc) as ranking from sales group by region, person ) temp where ranking = 1 

Note that this also works for links, as rank () seems to put the same ranking on equal amounts.

Answer 2

You can use the max () aggregate. It is probably less effective than other alternatives because you will do the group twice

 SELECT region,person,max(thousands) FROM (SELECT region, person, count(*) as thousands FROM sales GROUP BY region, person) tmp GROUP BY region, person ORDER BY region, max(thousands) desc 

Answer 3

Using Sql Server 2005+, you can do this using ROW_NUMBER ()

Take a look at this complete example.

 DECLARE @sales TABLE( region VARCHAR(50), person VARCHAR(50), Sales FLOAT ) INSERT INTO @sales SELECT 'canada','mike smith',1 INSERT INTO @sales SELECT 'canada','mike smith',1 INSERT INTO @sales SELECT 'canada','mike smith',1 INSERT INTO @sales SELECT 'canada','mike smith',1 INSERT INTO @sales SELECT 'canada','joe blog',1 INSERT INTO @sales SELECT 'canada','joe blog',1 INSERT INTO @sales SELECT 'canada','pete dodd',1 INSERT INTO @sales SELECT 'usa','john doe',1 INSERT INTO @sales SELECT 'usa','john doe',1 INSERT INTO @sales SELECT 'usa','jane smyth',1 INSERT INTO @sales SELECT 'europe','pieter tsu',1 INSERT INTO @sales SELECT 'europe','pieter tsu',1 INSERT INTO @sales SELECT 'europe','mike lee',1 ;WITH Counts AS( SELECT region, person, count(*) as thousands FROM @sales GROUP BY region, person ), CountVals AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY region ORDER BY thousands DESC) ROWID FROM Counts ) SELECT * FROM CountVals WHERE ROWID = 1 

Answer 4

In SQL Server 2005 and above, use ROW_NUMBER with PARTITION BY . The following should work (not tested and probably can be shortened):

 WITH total_sales AS (SELECT region, person, count(*) as thousands FROM sales GROUP BY region, person ORDER BY region, count(*) desc ) , ranked_sales AS (SELECT region, person, thousands, ROW_NUMBER() OVER (PARTITION BY region ORDER BY thousands DESC, person) AS region_rank FROM total_sales ) SELECT region, person, thousands FROM ranked_sales WHERE region_rank = 1 

Answer 5

First of all, I don't understand why count (*) is in $. My solution is similar to the existing one, but shorter, and I find it faster

 select top 1 with ties region, person, rank() over(partition by region order by count(*) desc) from sales group by region, person order by 3 

Answer 6

This is not too complicated. This query will do exactly what you want.

  select distinct region, (select top 1 person from Sales s2 where s2.region = s1.region group by person order by SUM(dollars) desc) as person, (select top 1 SUM(dollars) from Sales s2 where s2.region = s1.region group by person order by SUM(dollars) desc) as thousands from sales s1 

Answer 7

find the 5 best sellers by sales in each region

 select * from ( select region, [Customer Name], rank() over(partition by region order by sum(sales) desc) as ranking from Orders group by region, [Customer Name] ) temp where ranking between 1 and 5