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

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish