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

Your query has no ORDER BY, so the engine is free to present the rows in whatever order it likes, even if you do add row numbers. If you added ROW_NUMBER() OVER (ORDER BY SUM(Quantity) DESC, SUM(LineTotalInDefaultCurrency) DESC) AS Row you'd get the same ordering as the RANK (you can add tiebreakers if you like). Then wrap the whole thing as a subquery and ORDER BY Row. Or just order by the rank directly, if you don't really need the row number except to have an order. — Jeroen Mostert
– Jeroen Mostert, Commented Aug 9, 2017 at 10:18

Gordon Linoff · Accepted Answer · 2017-08-09 10:48:05Z

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];

answered Aug 9, 2017 at 10:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sepupic · Accepted Answer · 2017-08-09 10:36:07Z

1

From your desired resultset it seems you want the same order for your row_number but within the same rank additional order by Name:

with cte as
(
SELECT  
   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
)

select *,    
       RANK() OVER (ORDER BY UnitsSold DESC, RevenueDefaultCurrency DESC) AS SalesRank,
       ROW_NUMBER() OVER (ORDER BY UnitsSold DESC, RevenueDefaultCurrency desc, Name ) AS [row] 
from cte;

edited Aug 9, 2017 at 10:36

answered Aug 9, 2017 at 10:23

sepupic

8,7371 gold badge11 silver badges21 bronze badges

2 Comments

level_zebra Over a year ago

Thanks, that is almost what I require, However it returns a different number of results. Your query returns an 20 extra rows

sepupic Over a year ago

Yes, you are right, row_number and rank should be calculated out of group by, I updated my answer

Collectives™ on Stack Overflow

SQL Server, Using Row Number

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related