Top 10 by Customer AND Location

Question

Similar to my last post, but digging a little deeper. My next task with this report is to give a list of Top 10 Customers per location - example: Top 10 Customers at 00468, 00469, 00471, etc.

select top 10 T1.LocationID, T1.CustName, T1.Quantity--, t1.Salesperson
from
(
select 
     SUM(Tkscale.Qty)Quantity,
        Slcust.Name CustName,
        Slperson.Name Salesperson,
        Inlocat.LocationID LocationID,
        Inlocat.Description Location
    from 
Tkscale
      left outer join Slcust on Tkscale.CustomerID = Slcust.CustomerID 
      left outer join Slperson on slcust.SalespersonID = Slperson.SalespersonID
      left outer join Inlocat on Inlocat.LocationID = Tkscale.LocationID
     where 
slperson.Name like 'Tammy%'
   group by 
Inlocat.LocationID, inlocat.description, Tkscale.CustomerID, Slcust.Name, Slperson.Name
) T1
order by  T1.Quantity desc, T1.LocationID, T1.CustName

output:

aaaaand....are you getting some error?, not the results that you want? — Lamak, Jun 21 '16 at 17:19
Not the results I want. I'm getting a Top 10 by customer, regardless of the location. I want a Top 10 by location. — Molly, Jun 21 '16 at 17:20
So you want top 10 in 1234, top 10 in 5678, top 10 in 76543 etc - right? For every location, you want the top 10 of that location? And you want this in a single result set? — Don Cheadle, Jun 21 '16 at 17:23
Overall, I think this is very hard... and you may find help searching more for what you're trying to do. E.g. this question may help http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category — Don Cheadle, Jun 21 '16 at 17:25
Yes. I understand that the results will not be 10 lines...but should be 10 lines per location - and that's exactly what we want. — Molly, Jun 21 '16 at 17:25
What about ties, do you want only 10 or the top 10 even with ties. Using RANK or DENSE_RANK depending on your answer to that will be your key. https://msdn.microsoft.com/en-us/library/ms173825.aspx — Matt, Jun 21 '16 at 17:59

score 0 · Answer 1 · answered Jun 21 '16 at 17:29

use row_number

SELECT  T1.LocationID
    ,T1.CustName
    ,T1.Quantity --, t1.Salesperson
FROM (
    SELECT SUM(Tkscale.Qty) Quantity
        ,Slcust.NAME CustName
        ,Slperson.NAME Salesperson
        ,Inlocat.LocationID LocationID
        ,Inlocat.Description Location
        ,row_number() over (partition by Inlocat.LocationID order by SUM(Tkscale.Qty) desc) rowid 
    FROM Tkscale
    LEFT JOIN Slcust
        ON Tkscale.CustomerID = Slcust.CustomerID
    LEFT JOIN Slperson
        ON slcust.SalespersonID = Slperson.SalespersonID
    LEFT JOIN Inlocat
        ON Inlocat.LocationID = Tkscale.LocationID
    WHERE slperson.NAME LIKE 'Tammy%' 
    GROUP BY Inlocat.LocationID
        ,inlocat.description
        ,Tkscale.CustomerID
        ,Slcust.NAME
        ,Slperson.NAME
    ) T1
where t1.rowid < 11
ORDER BY T1.LocationID
    ,T1.rowid desc

Good stuff! That gets me alot closer - thanks to both of you! — Molly, Jun 21 '16 at 17:40

Matt · Answer 2 · 2016-06-21T18:14:49.683

select T1.LocationID, T1.CustName, T1.Quantity--, t1.Salesperson
from
(
select 
     SUM(Tkscale.Qty)Quantity,
        Slcust.Name CustName,
        Slperson.Name Salesperson,
        Inlocat.LocationID LocationID,
        Inlocat.Description Location
       ,NoGapsWithTies = DENSE_RANK() OVER (PARTITION BY Inlocat.LocationID ORDER BY SUM(Tkscale.Qty) DESC)
       ,WithGapsAndTies = RANK() OVER (PARTITION BY Inlocat.LocationID ORDER BY SUM(Tkscale.Qty) DESC)
       ,NoGapsNoTiesAbitraryChoiceIfTie = ROW_NUMBER() OVER (PARTITION BY Inlocat.LocationID ORDER BY SUM(Tkscale.Qty) DESC)
    from 
Tkscale
      left outer join Slcust on Tkscale.CustomerID = Slcust.CustomerID 
      left outer join Slperson on slcust.SalespersonID = Slperson.SalespersonID
      left outer join Inlocat on Inlocat.LocationID = Tkscale.LocationID
     where 
slperson.Name like 'Tammy%'
   group by 
Inlocat.LocationID, inlocat.description, Tkscale.CustomerID, Slcust.Name, Slperson.Name
) T1
WHERE
    --NoGapsWithTies <=10
    WithGapsAndTies <= 10
    --NoGapsNoTiesAbitraryChoiceIfTie <=10
order by  T1.Quantity desc, T1.LocationID, T1.CustName

This works because prior to the window functions being calculated the result set is built by the group by. using DENSE_RANK will give you ties with no gaps, RANK will give you ties and gaps, ROW_NUMBER will give you no ties and no gaps but arbitrarily choose when their is a tie between the customers.

Top 10 by Customer AND Location

2 Answers2