SQL Server, Using Row Number

Question

I have the below query which returns a list of items sold ordered by the amount of units sold. The item which has sold the most being ranked 1 and then subsequent items ranked in ascending order.

SELECT  
      RANK() OVER (ORDER BY SUM(Quantity) DESC,       
      SUM(LineTotalInDefaultCurrency) DESC) AS SalesRank,
      P.Name ,SUM(Quantity) as UnitsSold ,
      SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency, 
      sf.ProductId
FROM 
      SalesFact sf 
INNER JOIN 
      Product p ON sf.ProductId = p.ProductId 
WHERE 
      Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' GROUP BY sf.ProductId, p.Name

It returns a list of results like this

SalesRank   Name                                UnitsSold   RevenueDefaultCurrency  ProductId   
1           Energy Saving Dryer Balls           1230        6429.58         1086381 
2           Universal Dishwasher Cutlery Basket 654         4700.64         1107301 
3           Limescale and Detergent Remover     361         4106.00         664212  
4           Universal Extendable Oven Shelf     364         3885.77         655005  
5           2500 Watt Fan Oven Element          157         1532.72         1019719 
6           Filter Vacuum Bags NVM-1CH          273         2320.88         479302  
7           Universal Dishwasher Cutlery Basket 81          1954.66         511673  
8           Ice Cube Tray                       10          20.99           655045
8           Vacuum Filter - Pack of 2           10          20.99           470556
8           Vacuum Post Motor Filter            10          20.99           1562181

I am trying to add a row number to the resulting query so my results would look like

Row SalesRank   Name                                UnitsSold   RevenueDefaultCurrency  ProductId   
1   1           Energy Saving Dryer Balls           1230        6429.58         1086381     
2   2           Universal Dishwasher Cutlery Basket 654         4700.64         1107301     
3   3           Limescale and Detergent Remover     361         4106.00         664212      
4   4           Universal Extendable Oven Shelf     364         3885.77         655005  
5   5           2500 Watt Fan Oven Element          157         1532.72         1019719 
6   6           Filter Vacuum Bags NVM-1CH          273         2320.88         479302  
7   7           Universal Dishwasher Cutlery Basket 81          1954.66         511673  
8   8           Ice Cube Tray                       10          20.99           655045
9   8           Vacuum Filter - Pack of 2           10          20.99           470556
10  8           Vacuum Post Motor Filter            10          20.99           1562181

I have been trying to use ROW_NUMBER() to achieve this. At the moment I have amended my query as such to include ROW_NUMBER(), so I now have

SELECT  
     RANK() OVER (ORDER BY SUM(Quantity) DESC, 
     SUM(LineTotalInDefaultCurrency) DESC) AS SalesRank,
     p.Name ,SUM(Quantity) as UnitsSold ,
     SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency,      
     sf.ProductId,
     ROW_NUMBER() OVER(ORDER BY COUNT(sf.ProductId) DESC, sf.ProductId) AS [row]
 FROM 
     SalesFact sf INNER JOIN Product p ON sf.ProductId = p.ProductId 
 WHERE 
     Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' 
 GROUP BY 
     sf.ProductId, p.Name

However I cant get the ordering correct. If I add ORDER BY row then the SalesRank is out of order, If I add ORDER by SalesRank then the results are not ordered by Row

I hope that makes sense. Can anyone advise how I can acheve a resultset like the above. Thanks

Answer 1

I think your row definition is off:

SELECT ROW_NUMBER() OVER (ORDER BY SUM(Quantity) DESC, 
                                   SUM(LineTotalInDefaultCurrency) DESC
                         ) AS row,
       RANK() OVER (ORDER BY SUM(Quantity) DESC, 
                             SUM(LineTotalInDefaultCurrency) DESC
                   ) AS SalesRank,
       p.Name, SUM(Quantity) as UnitsSold ,
       SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency,      
       sf.ProductId,   
FROM SalesFact sf INNER JOIN
     Product p
     ON sf.ProductId = p.ProductId 
WHERE Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' 
GROUP BY sf.ProductId, p.Name
ORDER BY [row];