CODEWITHSUNDEEP
sqlt-sqlgreatest-n-per-group
Etienne
Etienne

Reputation: 7201

Return top 10 per year

I need to return the top 10 products per year, how can I do this with my following query?

SELECT 
   DP.ProductID
   , DP.Name
   , Year(FS.OrderDate) as TheYear
   , FS.OrderQty
   , FS.OrderAmount
FROM dbo.DimProduct AS DP
LEFT JOIN dbo.FactSales as FS on FS.ProductID = DP.ProductID

Upvotes: 2

Views: 2400

Answers (3)

Gimmy
Gimmy

Reputation: 3911

SELECT 
   DP.ProductID
   , DP.Name
   , Year(FS.OrderDate) as TheYear
   , FS.OrderQty
   , FS.OrderAmount 
   , (FS.OrderQty * FS.OrderAmount) AS FS.Total
FROM dbo.DimProduct AS DP
LEFT JOIN dbo.FactSales as FS on FS.ProductID = DP.ProductID
GROUP BY TheYear, DP.ProductID, FS.Total
ORDER BY FS.Total DESC
WHERE seqnum <= 10;

Upvotes: 0

John Woo
John Woo

Reputation: 263763

This should be easy if your RDBMS supports Window Functions

SELECT  ProductID,
        Name,
        TheYear,
        OrderQty,
        OrderAmount
FROM    
        (
            SELECT  DP.ProductID
                    ,DP.Name
                    ,Year(FS.OrderDate) as TheYear
                    ,FS.OrderQty
                    ,FS.OrderAmount,
                    ,ROW_NUMBER() OVER() (PARTITION BY Year(FS.OrderDate) 
                                        ORDER BY FS.OrderQty DESC) rn
            FROM    dbo.DimProduct AS DP
                    LEFT JOIN dbo.FactSales as FS 
                        on FS.ProductID = DP.ProductID
        ) s
WHERE   rn <= 10
ORDER   BY TheYear

The current query will give you 10 products for every TheYear based on FS.OrderQty since you have not mentioned the criteria on how the records will be sorted out.

The ROW_NUMBER() (a RANKING function) will generate a sequence of number for each group, in this case Year(FS.OrderDate), that is sorted out based on FS.OrderQty. The records will then be filtered out based on the value of the generated sequence.

However, if ROW_NUMBER() will not generate TIE on the records having the same FS.OrderQty. If you want it to be handled, use DENSE_RANK() instead of ROW_NUMBER().

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You want to use the function row_number() to get the top 10. This assumes that OrderQty defines the top 10:

select t.*
from (SELECT DP.ProductID, DP.Name, Year(FS.OrderDate) as TheYear, FS.OrderQty, FS.OrderAmount,
             row_number() over (partition by Year(FS.OrderDate)
                                order by fs.OrderAmount desc
                               ) as seqnum
      FROM dbo.DimProduct DP LEFT JOIN
           dbo.FactSales FS
           on FS.ProductID = DP.ProductID
    ) t
where seqnum <= 10;

The function row_number() enumerates rows, starting with 1. It starts over within each group, as defined by the partition by clause (in your case, the year). The ordering of the numbers is based on the order by clause (in your case, fs.OrderAmount desc). So, the ten best products in each year will have the numbers 1-10 and the where clause just chooses them.

Upvotes: 3

Related Questions