top 3 products by number of units shipped in the month

Question

I need to list the names of the top 3 products by number of units shipped and the names of the top 3 customers by total amount invoiced. And I must use the column SalesOrderHeader.OrderDate as reference date with the constraint of dbo.SalesOrderHeader.Status.

In the resulting table, I need to display:

1. Month : month in YYYY-MM format
2. Product_N1 : Product with the highest number of units shipped in the month
3. Product_N2 : Product with 2nd. highest number of units shipped in the month
4. Product_N3 : Product with 3rd. highest number of units shipped in the month
5. Client_N1: Client with the highest billing dispatched in the month
6. Client_N2 : Client with 2nd. highest billing dispatched in the month
7. Client_N3 : Client with 3rd. highest billing dispatched in the month

I have my query, but I don't know how to get the top 2 and top 3 could someone help me or guide me? This is my query

SELECT  top 1   
FORMAT(a.ShipDate, 'yyyy-MM') as Month, 
p1.Name AS Product_N1,
p1.quantity, 
p2.Name as Product_N2 ,
p2.quantity
FROM SalesOrderHeader as a 
inner join (select  d.ShipDate,c.Name,SUM(b.OrderQty) as quantity FROM SalesOrderDetail as b
            inner join Product as c on b.ProductID =c.ProductID inner join SalesOrderHeader as d 
            on b.SalesOrderID = d.SalesOrderID where d.Status = 5 group by d.ShipDate,c.Name, d.ShipDate)
            as p1 on p1.ShipDate = a.ShipDate
inner join (select  d.ShipDate,c.Name,SUM(b.OrderQty) as quantity FROM SalesOrderDetail as b
            inner join Product as c on b.ProductID =c.ProductID inner join SalesOrderHeader as d 
            on b.SalesOrderID = d.SalesOrderID where d.Status = 5 group by d.ShipDate,c.Name, d.ShipDate)
            as p2  on p2.ShipDate = p1.ShipDate
where p2.quantity> p1.quantity
order by 3 desc

Answer 1

Please see the code below. Please try again as there may be a syntax error:

select TOP 3 DENSE_RANK() OVER (ORDER BY sumorder DESC) OrderNo, *
INTO #tempsumorder
from
(
    select  d.ShipDate,c.Name,SUM(b.OrderQty) as quantity 
    FROM SalesOrderDetail as b
    inner join Product as c on b.ProductID =c.ProductID 
    inner join SalesOrderHeader as d on b.SalesOrderID = d.SalesOrderID 
    where d.Status = 5 
    group by d.ShipDate,c.Name, d.ShipDate
) tmp
order by sumorder desc

select 
    (select FORMAT(a.ShipDate, 'yyyy-MM') as Month, Name AS Product_N1, quantity from #tempsumorder where OrderNo = 1) --No 1
    ,(select Name AS Product_N2, quantity from #tempsumorder where OrderNo = 2) --No 2
    ,(select Name AS Product_N2, quantity from #tempsumorder where OrderNo = 3) --No 3
 
DROP TABLE #tempsumorder

If you need to get the largest data in the month, then put the month and year conditions in this code to make it reasonable.