sql - Top 3 rows per country -

i've produced report of "which , how many of products been sold in each country". i'm using northwind database, sql server 2012.

the following code:

select      o.shipcountry 'country',od.productid,     p.productname, p.unitprice,     sum(od.quantity) 'number of units sold'      products p inner join      [order details] od on od.productid = p.productid inner join      orders o on o.orderid = od.orderid group      p.productname, od.productid, p.unitprice, o.shipcountry order      o.shipcountry, 'number of units sold' desc 

the result shows on 900 rows, each country has 10 20 rows:

but want take notch, , want produce "top 3 products sold per country" tried row_number() on (partition by i'm clumsy @ using row_number()

the below wrong code:

with cte (    select        o.shipcountry 'country',od.productid,       p.productname, p.unitprice,       sum(od.quantity) 'number of units sold',       row_number() on (partition o.shipcountry order ('number of units sold') desc) 'number of units sold'           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'      cte       'number of units sold' < 4 group      p.productname, od.productid, p.unitprice, o.shipcountry order      o.shipcountry desc 

try this:

with cte  (     select          o.shipcountry, od.productid,         p.productname, p.unitprice,         sum(od.quantity) unitssold,         rownum = row_number() on (partition o.shipcountry order sum(od.quantity) desc)              products p     inner join          [order details] od on od.productid = p.productid     inner join          orders o on o.orderid = od.orderid     group          p.productname, od.productid, p.unitprice, o.shipcountry ) select *  cte cte.rownum <= 3 

basically, in cte, define columns want - word of caution: don't use column names spaces , stuff that! makes nice presentation on screen, hard use in query!

then add row_number() number each entry each country starting @ 1.

and finally, select cte, , take rows rownum <= 3 ==> top 3 each country.