SQL Query to return the Top 2 Values

Question

I am trying to return the top 2 most ordered items in our customer database. Below is what I have for the most ordered item but I am having trouble figuring out how to create another column for the 2nd most ordered item.

What is the best way to create the 2nd column?

SELECT FirstName, EmailAddress, Id, PreferredLocationId, 
(
     SELECT TOP 1 [Description] FROM [Order] o 
     INNER JOIN [OrderItem] oi ON oi.OrderId = o.OrderId
     WHERE o.CustomerId = Customer.Id
     GROUP BY [Description]
     ORDER BY COUNT(*) DESC
) AS MostOrderedItem
FROM Customer
GROUP BY FirstName, EmailAddress, Id, PreferredLocationId

Try using 2 CTE's and do a select from each – JsonStatham Nov 12 '14 at 17:14 — JsonStatham, Nov 12 '14 at 17:14

Daniel Gimenez · Accepted Answer · 2014-11-12T17:53:48.113

Lot's of different ways to handle this if you're using SQL Server 2012. I'm going to use a CTE to get the first two rows and use ROW_NUMBER()

WITH cte AS (
  SELECT CustomerId, [Description]
    , ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY COUNT(*) DESC) [RowID]
  FROM [Order] o 
  INNER JOIN [OrderItem] oi ON oi.OrderId = o.OrderId
  GROUP BY CustomerId, [Description]
)
SELECT FirstName, EmailAddress, Id, PreferredLocationId, cte1.Description, cte2.Description
FROM Customer
LEFT JOIN cte cte1 ON cte1.CustomerID = Customer.CustomerId AND cte1.RowID = 1
LEFT JOIN cte cte2 ON cte2.CustomerID = Customer.CustomerId AND cte2.RowID = 2

The Common Table Expression creates the list of all customers, descriptions and their row number. Note that if you have ties, you're not guarunteed which description will come first. You can add to to the windowing function description so that if there is a tie, whatever comes first in the alphabet will be the tie breaker.

Daniel - what if I also wanted to add the count of the most ordered item? — marshpipes, Nov 12 '14 at 18:22

SQL Query to return the Top 2 Values

1 Answers1