Top 3 rows per country

Question

I've produced a report of "Which and how many of Products been sold in each country". I'm using Northwind database, SQL Server 2012.

The following is the code:

SELECT 
    o.ShipCountry AS 'Country',od.ProductID,
    p.ProductName, p.UnitPrice,
    SUM(od.Quantity) AS 'Number of Units Sold'
FROM 
    Products p
INNER JOIN 
    [Order Details] od ON od.ProductID = p.ProductID
INNER JOIN 
    Orders o ON o.OrderID = od.OrderID
GROUP BY 
    p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
ORDER BY 
    o.ShipCountry, 'Number of Units Sold' DESC

The result shows over 900 rows, each country has about 10 to 20 rows:

But I want to take it up a notch, and now I want to produce "Top 3 products sold per country" So I tried ROW_NUMBER() OVER (PARTITION BY but I'm clumsy at using Row_NUMBER()

The below is my wrong code:

WITH CTE AS
(
   SELECT 
      o.ShipCountry AS 'Country',od.ProductID,
      p.ProductName, p.UnitPrice,
      SUM(od.Quantity) AS 'Number of Units Sold',
      ROW_NUMBER() OVER (PARTITION BY o.ShipCountry ORDER BY ('Number of Units Sold') DESC) AS 'Number of Units Sold'
   FROM 
      Products p
   INNER JOIN 
      [Order Details] od ON od.ProductID = p.ProductID
   INNER JOIN 
      Orders o ON o.OrderID = od.OrderID)
SELECT 
    'Country', ProductID, 
     ProductName, UnitPrice, 'Number of Units Sold'
FROM 
    CTE 
WHERE 
    'Number of Units Sold' < 4
GROUP BY 
    p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
ORDER BY 
    o.ShipCountry DESC

Answer 1

Try this:

WITH CTE AS 
(
    SELECT 
        o.ShipCountry, od.ProductID,
        p.ProductName, p.UnitPrice,
        SUM(od.Quantity) AS UnitsSold,
        RowNum = ROW_NUMBER() OVER (PARTITION BY o.ShipCountry ORDER BY SUM(od.Quantity) DESC)
    FROM 
        Products p
    INNER JOIN 
        [Order Details] od ON od.ProductID = p.ProductID
    INNER JOIN 
        Orders o ON o.OrderID = od.OrderID
    GROUP BY 
        p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
)
SELECT * 
FROM CTE
WHERE CTE.RowNum <= 3

Basically, in the CTE, you define the columns you want - word of caution: don't use column names with spaces and stuff like that! Makes for a nice presentation on screen, but really hard to use in a query!

Then you add the ROW_NUMBER() that will number each entry for each country starting at 1.

And finally, you select from the CTE, and you take only those rows with a RowNum <= 3 ==> the TOP 3 for each country.

Answer 2

;with CTE as(
SELECT o.ShipCountry AS 'Country',
       od.ProductID,
       p.ProductName,
       p.UnitPrice,
       SUM(od.Quantity) AS 'Number of Units Sold'
FROM Products p
INNER JOIN [Order Details] od
ON od.ProductID=p.ProductID
INNER JOIN Orders o
ON o.OrderID=od.OrderID
GROUP BY p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
) 
,CTE2 as
(    Select 
       CTE.Country,
       CTE.ProductID,
       CTE.ProductName,
       CTE.UnitPrice,
       CTE.[Number of Units Sold],
       ROW_NUMBER() OVER (PARTITION BY CTE.Country 
       ORDER BY CTE.[Number of Units Sold] DESC) AS rownum
     from CTE
)
select CTE2.Country,
       CTE2.ProductID,
       CTE2.ProductName,
       CTE2.UnitPrice,
       CTE2.[Number of Units Sold]
FROM CTE2 
WHERE CTE2.rownum<4
ORDER BY CTE2.Country, CTE2.[Number of Units Sold] DESC   

Answer 3

Try this :

SELECT Country, ProductID, 
ProductName,UnitPrice,Number_of_Units_Sold
FROM 
( 
SELECT o.ShipCountry AS Country, od.ProductID as ProductID,
p.ProductName as ProductName, p.UnitPrice as UnitPrice,
SUM(od.Quantity) AS Number_of_Units_Sold,
ROW_NUMBER() OVER (PARTITION BY o.ShipCountry ORDER BY (SUM(od.Quantity)) DESC) AS MYRANK

FROM Products p
INNER JOIN [OrderDetails] od
ON od.ProductID=p.ProductID
INNER JOIN Orders o
ON o.OrderID=od.OrderID

GROUP BY o.ShipCountry, p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry

) tmp
where MYRANK <= 3
ORDER BY Country, Number_of_Units_Sold DESC