RANK and DENSE_RANK in SQL?

Have you ever come across the rating of SQL tables? How can we rank the tables in several ways? This article will show us how SQL ranking works and what it is.

RANK and DENSE_RANK in SQL?

The RANK() function gives each row a rank according to a specific ordering. One notable feature of this SQL rank function is it will apply the same rank to values which match, so it’s possible to receive two rank as 1 for example. It will then add the matched rows to produce the next rank, so if you have two search with rank 1 then your next rank will be 3 with a gap in the rankings where rank 2 would otherwise be. Therefore, when the rank increments, it always matches the number of rows corresponding to that rank.

RANK() OVER ([PARTITION BY partition_expression, ... ]
            ORDER BY sort_expression [ASC | DESC], ...)

Example

SELECT p.FirstName, p.LastName     
   ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"      
   ,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"           
   ,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"      
   ,s.SalesYTD, a.PostalCode  
FROM Sales.SalesPerson AS s   
   INNER JOIN Person.Person AS p   
       ON s.BusinessEntityID = p.BusinessEntityID      
   INNER JOIN Person.Address AS a   
       ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

RANK Result will be: 1,1,1,1,1,1,7,8,9

Though the RANK and DENSE_RANK functions share several similarities, the key distinction between them is that RANK may skip numbers when the same value appears many times and does not return consecutive values. While DENSE_RANK delivers the successive numbers

The SQL DENSE RANK function also ranks every row based on a certain order. The DENSE_RANK() function's defining result, therefore, is that it will not allow gaps in the ranking but will apply the same rank to values that match. Thus, as the rank rises, you cannot be certain whether the ranking and row count are identical.

DENSE_RANK() OVER ([PARTITION BY partition_expression, ... ]                   ORDER BY sort_expression [ASC | DESC], ...)

Example

SELECT p.FirstName, p.LastName     
   ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"       
   ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"      
   ,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"     
   ,s.SalesYTD, a.PostalCode  
FROM Sales.SalesPerson AS s   
   INNER JOIN Person.Person AS p   
       ON s.BusinessEntityID = p.BusinessEntityID     
   INNER JOIN Person.Address AS a   
       ON a.AddressID = p.BusinessEntityID 
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

DENSE_RANK Result will be: 1,1,1,1,1,1,2,3

Using the RANK function calls for several important considerations: 

• Using the RANK function calls for the use of the ORDER BY clause. 
• The PARTITION BY clause could be optional.
• If two records share identical numerical values- they will also share a similar ranking value.

 

Using the DENSE_RANK function calls for several important considerations:

• Rows with same values get the same Rank.
• Subsequent rows' Rank rises by one.