iniki iniki - 6 months ago 10
SQL Question

SQL Server - Find Top n customers with maximum orders

I want to find top 2 customers with maximum orders.

The table looks like:

CustomerId OrderId ProductId
101 1 A
101 3 B
101 4 C
102 9 D
102 9 E
103 11 E
103 22 F


This is the output that I need from SELECT query:

CustomerId OrderId
101 1
101 3
101 4
103 11
103 22


The solution is just not clicking to my mind...I have kind of reached half-way using following query -

SELECT CustomerId, OrderId
FROM dbo.CustomerOrder
GROUP BY CustomerId, OrderId


which just gives me distinct pairs of CustomerId, OrderId.

Can anyone please help.

Answer

Here is the SQL Fiddle example that shows the below code working:

SELECT DISTINCT CO.CustomerId, CO.OrderID FROM 
(
  SELECT TOP(2) COS.CustomerId, COUNT(DISTINCT COS.orderId) as NoOfOrders
  FROM custorders AS COS
  GROUP BY COS.CustomerId
  ORDER BY COUNT(DISTINCT COS.orderId) DESC, CustomerId  DESC
) AS COM 
INNER JOIN custorders AS CO
  ON COM.CustomerId = CO.CustomerId
Comments