SQL Group BY, top N items for each group

Question

I have an SQL query that receives the 5 best sold items in this store.

SELECT TOP 5 S.UPCCode, SUM(TotalDollarSales) FROM Sales S WHERE S.StoreId = 1 GROUP BY S.UPCCode ORDER BY SUM(S.TotalDollarSales) desc 

In the sales table there is → UPCCode, SaleDate, StoreId, TotalDollarSales

I am looking for a request that will return to me the Top 5 items sold for each of the stores in one request. I can write some queries and use a join, but this does not seem to be effective.

How can I get the 5 best sold items for each store in one request.

Thanks in advance.

Answer 1

 ;WITH s AS ( SELECT StoreID, UPCCode, tds, rn = ROW_NUMBER() OVER (PARTITION BY StoreID ORDER BY tds DESC) FROM ( SELECT StoreID, UPCCode, tds = SUM(TotalDollarSales) FROM Sales GROUP BY StoreID, UPCCode ) AS s2 ) SELECT StoreID, UPCCode, TotalDollarSales = tds FROM s WHERE rn <= 5 ORDER BY StoreID, TotalDollarSales DESC; 

Answer 2

try the following:

 select ss.StoreId,is.* from (select distinct StoreId from Sales) ss cross apply (SELECT TOP 5 S.UPCCode, SUM(TotalDollarSales) as SumTotalDollarSales FROM Sales S WHERE S.StoreId = ss.StoreId GROUP BY S.UPCCode ORDER BY SUM(S.TotalDollarSales) desc) is