DENSE_RANK

What is DENSE_RANK?

In the last two weeks we have already given you “joy” by numbering rows in the result set with the ROW_NUMBER and RANK functions. We want to continue with this today. The DENSE_RANK function is, as the name suggests, closely related to the RANK function from last week. So what is the difference? It’s relatively simple: while the RANK function skips a number when assigning the same number for two lines, the DENSE_RANK function skips nothing, so here you get a continuous sequence of numbers.

How can you work with DENSE_RANK?

Remember the query we used last week using the RANK function. We created a query in the Wide World Importers database that returns sortable items, but the sorting does not have to be unique. We used a CTE to count the number of orders in which each product appeared in the OrderLines and calculated the popularity of the respective product with the RANK function:

WITH cte_sales AS (
    SELECT 
         StockItemID
        ,COUNT(*) AS nOrders
        ,SUM(Quantity*UnitPrice) AS TotalSales 
    FROM 
        Sales.OrderLines
    GROUP BY StockItemID
)
SELECT  
     StockItemID
    ,nOrders
    ,TotalSales
    ,ROW_NUMBER() OVER (ORDER BY nOrders DESC, TotalSales DESC) as Popularity
    ,RANK() OVER (ORDER BY nOrders DESC) as Real_Popularity
FROM cte_sales 
ORDER BY 
     nOrders DESC
    ,TotalSales DESC

As a result, both products, 104 and 120 had received the (real) popularity 1, while the next product (the one with ID 167) was assigned the third place, similar to what is usual in sports competitions.

Now, in order not to skip a rank, we add another column to the query:

WITH cte_sales AS (
    SELECT 
         StockItemID
        ,COUNT(*) AS nOrders
        ,SUM(Quantity*UnitPrice) AS TotalSales 
    FROM 
        Sales.OrderLines
    GROUP BY StockItemID
)
SELECT  
     StockItemID
    ,nOrders
    ,TotalSales
    ,ROW_NUMBER() OVER (ORDER BY nOrders DESC, TotalSales DESC) as Popularity
    ,RANK() OVER (ORDER BY nOrders DESC) as Real_Popularity
    ,DENSE_RANK() OVER (ORDER BY nOrders DESC) as Dense_Real_Popularity
FROM cte_sales 
ORDER BY 
     nOrders DESC
    ,TotalSales DESC

In the result set, the product 167 now has the Dense_Real_Popularity 2 instead of the Real_Popularity 3. And to round things up, let’s take a look at the most popular items:

WITH cte_sales AS (
    SELECT 
         StockItemID
        ,COUNT(*) AS nOrders
        ,SUM(Quantity*UnitPrice) AS TotalSales 
    FROM 
        Sales.OrderLines
    GROUP BY StockItemID
)
SELECT  
     cs.StockItemID
    ,si.StockItemName
    ,nOrders
    ,TotalSales
    ,ROW_NUMBER() OVER (ORDER BY nOrders DESC, TotalSales DESC) as Popularity
    ,RANK() OVER (ORDER BY nOrders DESC) as Real_Popularity
    ,DENSE_RANK() OVER (ORDER BY nOrders DESC) as Dense_Real_Popularity
FROM cte_sales cs 
LEFT JOIN 
    Warehouse.StockItems si 
ON cs.StockItemID = si.StockItemID
ORDER BY 
     nOrders DESC
    ,TotalSales DESC

So it was green battery-operated slippers and a hoodie with an alien officer that were particularly popular with Wide World Importers’ customers. The ninja in us would have guessed Shuriken, after all, these are expendable items…

References

• Official Microsoft documentation