back to blog index

Title

Given the Orders table in the Northwind database, the task is to write a
query that returns for each customer the employee who handled the highest
number of orders for the customer.

Try to solve two variations of the problem:

1. If multiple employees handled the same highest number of orders, return
all of them. In each output row, return the customer id, employee id and
count of orders. The output should look like this:

CustomerID EmployeeID  Cnt
---------- ----------- -----------
ALFKI      1           2
ALFKI      4           2
ANATR      3           2
ANTON      3           3
AROUT      4           4
BERGS      3           6
BLAUS      9           3
BLONP      4           3
BOLID      4           2
BONAP      4           4
BOTTM      3           4
BSBEV      3           2
BSBEV      4           2
BSBEV      6           2
CACTU      8           2
...

(142 row(s) affected)
2. If multiple employees handled the same highest number of orders, return
the one with the highest employee id. The output should look like this:
CustomerID EmployeeID  Cnt
---------- ----------- -----------
ALFKI      4           2
ANATR      3           2
ANTON      3           3
AROUT      4           4
BERGS      3           6
BLAUS      9           3
BLONP      4           3
BOLID      4           2
BONAP      4           4
BOTTM      3           4
BSBEV      6           2
CACTU      8           2
...

(89 row(s) affected)

Cheers
--
BG

End of Article

You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Query 1 SELECT OUTER1.* FROM (SELECT CustomerID, EmployeeId , COUNT(*) AS OrdersCount FROM Orders O1 GROUP BY CustomerId , EmployeeID ) AS OUTER1 CROSS APPLY ( SELECT CustomerID, MAX(OrdersCount) AS MaxOrders FROM ( SELECT CustomerID, EmployeeId , COUNT(*) AS OrdersCount FROM Orders GROUP BY CustomerId , EmployeeID ) AS a GROUP BY CustomerID ) AS Max1 WHERE OUTER1.CustomerID = Max1.CustomerID AND OUTER1.OrdersCount = Max1.MaxOrders ORDER by 1

Query2:

SELECT OUTER1.CustomerID, max(outer1.EmployeeId), max(outer1.OrdersCount) FROM (SELECT CustomerID, EmployeeId , COUNT(*) AS OrdersCount FROM Orders O1 GROUP BY CustomerId , EmployeeID ) AS OUTER1 CROSS APPLY ( SELECT CustomerID, MAX(OrdersCount) AS MaxOrders FROM ( SELECT CustomerID, EmployeeId , COUNT(*) AS OrdersCount FROM Orders GROUP BY CustomerId , EmployeeID ) AS a GROUP BY CustomerID ) AS Max1 WHERE OUTER1.CustomerID = Max1.CustomerID AND OUTER1.OrdersCount = Max1.MaxOrders GROUP BY OUTER1.CustomerID ORDER by 1

paPai- September 29, 2007

Article Rating 5 out of 5

the cross apply can be replaced by an inner join both queries take the same execution plan

paPai- September 29, 2007

Article Rating 5 out of 5

Nested aggregates, before SS2005 this was quite awfull, but now there's WITH or (my favourite) OLAP functions: Easy to write and easy to extend Q1 SELECT CustomerID, EmployeeId, OrdersCount FROM ( SELECT CustomerID, EmployeeId , COUNT(*) AS OrdersCount, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(*) DESC) AS r FROM Orders GROUP BY CustomerId , EmployeeID ) dt WHERE r = 1 ORDER BY 1, 2

Q2 SELECT CustomerID, EmployeeId, OrdersCount FROM ( SELECT CustomerID, EmployeeId , COUNT(*) AS OrdersCount, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY COUNT(*) DESC, EmployeeId DESC) AS r FROM Orders GROUP BY CustomerId , EmployeeID ) dt WHERE r = 1 ORDER BY 1

dnoeth- October 04, 2007

Article Rating 5 out of 5

This solution also works on 2000.

1st Part:

SELECT a.CustomerId, a.EmployeeId, COUNT(*) cnt FROM Orders a INNER JOIN (SELECT CustomerId, MAX(cnt) cnt FROM (SELECT CustomerId, EmployeeId, COUNT(*) cnt FROM Orders GROUP BY CustomerId, EmployeeId) a GROUP BY CustomerId) b ON a.CustomerId = b.CustomerId GROUP BY a.CustomerId, a.EmployeeId, b.cnt HAVING COUNT(*) = b.cnt

2nd part: SELECT c.CustomerId, MAX(c.EmployeeId), c.cnt FROM (SELECT a.CustomerId, a.EmployeeId, COUNT(*) cnt FROM Orders a INNER JOIN (SELECT CustomerId, MAX(cnt) cnt FROM (SELECT CustomerId, EmployeeId, COUNT(*) cnt FROM Orders GROUP BY CustomerId, EmployeeId) a GROUP BY CustomerId) b ON a.CustomerId = b.CustomerId GROUP BY a.CustomerId, a.EmployeeId, b.cnt HAVING COUNT(*) = b.cnt) c GROUP BY c.CustomerId, c.cnt ORDER BY 1, 2

rlahoty- October 04, 2007

Article Rating 5 out of 5

 



  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31    
or
More blogs about technology, databases, and SQL Server.
 

ADS BY GOOGLE