CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Mangesh Raut
Mangesh Raut

Reputation: 21

List the top 3 selling product for every year

The Question is "List the top 3 selling product for every year"

I executed following query 

select top 3 b.CalendarYear,c.ProductKey, d.EnglishProductSubcategoryName ,SUM(a.SalesAmount) as SALES
from FactInternetSales as A inner join dimdate as B
on a.OrderDateKey =b.DateKey
inner join DimProduct as c
on c.ProductKey = a.ProductKey
inner join DimProductSubcategory as d
on c.ProductSubcategoryKey = d.ProductSubcategoryKey
inner join  DimProductCategory as e 
on d.ProductCategoryKey=e.ProductCategoryKey
group by b.CalendarYear,c.ProductKey, d.EnglishProductSubcategoryName
order by SALES desc

i got following answer

CalendarYear    ProductKey  EnglishProductSubcategoryName    SALES
2006    312 Road Bikes  658401.68
2006    313 Road Bikes  608305.90
2006    310 Road Bikes  608305.90

My query is how only "year 2006" data came why not all years?

Upvotes: 1

Views: 2695

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Use the below query..

   ; WITH cte_1
     AS
     ( SELECT CalendarYear,ProductKey
                      ,EnglishProductSubcategoryName,SALES
                     ,ROW_NUMBER() OVER(PARTITION BY
                           CalendarYear,ProductKey
                          ,EnglishProductSubcategoryName
                       ORDER BY Sales DESC) RNO
         FROM (select b.CalendarYear,c.ProductKey
                                ,d.EnglishProductSubcategoryName
                                ,SUM(a.SalesAmount) as SALES
                       from FactInternetSales as A 
                           inner join dimdate as B
                                 on a.OrderDateKey =b.DateKey
                           inner join DimProduct as c
                                on c.ProductKey = a.ProductKey
                           inner join DimProductSubcategory as d
                                on c.ProductSubcategoryKey = d.ProductSubcategoryKey
                           inner join  DimProductCategory as e 
                                on d.ProductCategoryKey=e.ProductCategoryKey
                        group by b.CalendarYear,c.ProductKey
                          ,  d.EnglishProductSubcategoryName)t
  )
   SELECT CalendarYear,ProductKey
                      ,EnglishProductSubcategoryName,SALES
    FROM cte_1
    WHERE RNO<4
    ORDER BY CalendarYear DESC,RNO ASC

In the script provided you are only selecting the top3 records from the result set.so you are getting ony 3 records as output.

Upvotes: 2

baudo2048
baudo2048

Reputation: 1152

The general idea could be to add a column counter to each group of rows (1 2 3 ...) and then select the records were counter is less or equals to 3

Upvotes: 0

Related Questions