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 produces a complete list of sales people per region like this

    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 salesperson per region (as below), how can I best do that?

    region person      thousands

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

Answer 1

You can use the max() aggregate. It's probably less efficient than the other alternatives because you'll be doing group by twice

SELET 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 2

Using Sql Server 2005+ you could do this using a ROW_NUMBER()

Have a look at this full 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 3

In SQL Server 2005 and above use ROW_NUMBER with PARTITION BY. 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 4

First of all I do not understand why count(*) is in $. My solution is similar to existing, but shorter and I believe 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 5

I've done something like burnall suggested. I wasn't getting much love with the "top 1 with ties" part, so I made the whole thing a subquery and chose rows where ranking = 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 ties since rank() seems to place the same ranking on sums that are equal.

Answer 6

This isn't too difficult. 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