Mastering in DENSE_RANK() Function in SQL with Practical Examples

We provides an explanation of DENSE_RANK () function in SQL. It describes the purpose of the function, what it is used to do and in which cases it is to be preferred over other ranking functions such as RANK() or ROW_NUMBER().

Overview of DENSE_RANK()

DENSE_RANK () is an SQL window function. It gives each row within a division of the outcome set a rank founded on the divisional order of the row in a depicted column or expression. DENSE_RANK () does not omit ranks when a row ordering value is identical. It is handy in cases where you would like to list the N greatest things and would not like to forget any of them due to ties. This aspect is referred to as density hence the name DENSE_RANK ().

The ways that DENSE_RANK() differs with other ranking functions

DENSE_RANK () is similar to the RANK () function except that they deal with ties in different ways. rank () is rank skipping when ties; DENSE_RANK () is not. The two functions have an optional sort order parameter.

DENSE_RANK () behaves similarly to ROW_NUMBER () in that they assign the consecutive ranking without holes even when there are ties. The big distinction is that ROW_NUMBER () bases the ranking entirely on the partition column itself, but DENSE_RANK () uses the column you provide to order the ranks on.

Possibilities of use and consideration

DENSE_RANK () can be used when the top N items are to be listed. You also find it useful when you want a rank comparison of two things- e.g. in a ranking of best players. Then you prefer the rank of each player to be identical when considering all leaderboards, and you never want ties that create gaps in the ranks.

It is possible that other ranking functions perform better than DENSE_RANK(), since many rows can be ranked identically, remember that when you decide how to display data.

DENSE_RANK() syntax

The simplest syntax of the DENSE_RANK() function is the following one:

1. DENSE_RANK OVER (  
2. [PARTITION BY column1, column2,...]  
3. ORDER BY column3 [ ASC | DESC ]  
4. )  

• DENSE_RANK():The name of the function is DENSE_RANK().
• OVER(): Indicates which window on which the functionality is performed.
• PARTITION BY: Partitions the result set and groups them in partitions according to the described columns. This ranking is done in each of the partitions independently. In the case they are not given, their whole result set is regarded as a single partition.
• ORDER BY: Is the column or expression, by which the order of rows in each partition is defined. The ASC denotes ascending order (default) and DESC denotes descending order.

 Practical Examples of DENSE_RANK()

To illustrate the usage of DENSE_RANK(), let's consider a table named Employees with the following structure and data:

1. CREATE TABLE Employees (  
2.     EmployeeID INT PRIMARY KEY,  
3.     EmployeeName VARCHAR(255),  
4.     Department VARCHAR(255),  
5.     Salary DECIMAL(10, 2)  
6. );  
7.   
8. INSERT INTO Employees (EmployeeID, EmployeeName, Department, Salary) VALUES  
9. (1, 'Alice Smith', 'Sales', 60000.00),  
10. (2, 'Bob Johnson', 'Marketing', 75000.00),  
11. (3, 'Charlie Brown', 'Sales', 60000.00),  
12. (4, 'David Lee', 'IT', 90000.00),  
13. (5, 'Eve Wilson', 'Marketing', 75000.00),  
14. (6, 'Frank Miller', 'IT', 80000.00),  
15. (7, 'Grace Davis', 'Sales', 55000.00),  
16. (8, 'Henry Moore', 'Marketing', 70000.00),  
17. (9, 'Ivy Taylor', 'IT', 90000.00),  
18. (10, 'Jack White', 'Sales', 55000.00);  

  

1. --output  
2. EmployeeID  EmployeeName                   Department                     Salary  
3. ----------- ------------------------------ ------------------------------ ------------------------------  
4. 1           Alice Smith                    Sales                          60000.00  
5. 2           Bob Johnson                    Marketing                      75000.00  
6. 3           Charlie Brown                  Sales                          60000.00  
7. 4           David Lee                      IT                             90000.00  
8. 5           Eve Wilson                     Marketing                      75000.00  
9. 6           Frank Miller                   IT                             80000.00  
10. 7           Grace Davis                    Sales                          55000.00  
11. 8           Henry Moore                    Marketing                      70000.00  
12. 9           Ivy Taylor                     IT                             90000.00  
13. 10          Jack White                     Sales                          55000.00  

Example 1:Sorting the Employees on the basis of Salary

Here is the example of ranking employees by salary based on DENSE_RANK(). 

1. SELECT  
2.     EmployeeName,  
3.     Salary,  
4.     DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank  
5. FROM  
6.     Employees;  

 

1. --output  
2.   
3. EmployeeName                   Salary                         SalaryRank  
4. ------------------------------ ------------------------------ --------------------  
5. David Lee                      90000.00                       1  
6. Ivy Taylor                     90000.00                       1  
7. Frank Miller                   80000.00                       2  
8. Eve Wilson                     75000.00                       3  
9. Bob Johnson                    75000.00                       3  
10. Henry Moore                    70000.00                       4  
11. Alice Smith                    60000.00                       5  
12. Charlie Brown                  60000.00                       5  
13. Grace Davis                    55000.00                       6  
14. Jack White                     55000.00                       6  

 David Lee and Ivy Taylor earn the same amount of 90000.00 per year and, thus, have the highest (rank: 1). Frank Miller receives 80000.00 and a rank of 2. Bob Johnson and Eve Wilson earn 75000.00 respectively and therefore they have a matching rank of 3. The rank 4 group makes it appear where the data is dense.

Example 2: To Rank Employees based on Salary in Each Department

Here we will apply PARTITION BY and rank the employees by salary in each department. 

1. SELECT  
2.     EmployeeName,  
3.     Department,  
4.     Salary,  
5.     DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DepartmentSalaryRank  
6. FROM  
7.     Employees;  

 

1. --output  
2. EmployeeName                   Department                     Salary                         DepartmentSalaryRank  
3. ------------------------------ ------------------------------ ------------------------------ --------------------  
4. David Lee                      IT                             90000.00                       1  
5. Ivy Taylor                     IT                             90000.00                       1  
6. Frank Miller                   IT                             80000.00                       2  
7. Eve Wilson                     Marketing                      75000.00                       1  
8. Bob Johnson                    Marketing                      75000.00                       1  
9. Henry Moore                    Marketing                      70000.00                       2  
10. Alice Smith                    Sales                          60000.00                       1  
11. Charlie Brown                  Sales                          60000.00                       1  
12. Grace Davis                    Sales                          55000.00                       2  
13. Jack White                     Sales                          55000.00                       2  

 In this illustration, each department uses DENSE_RANK() function. Both David Lee and Ivy Taylor occupy the top ranking in the IT department with highest salary. The second highest salary goes to Frank Miller and the rank awarded is 2. The same argument is applicable to the Sales and Marketing departments. 

Example 3: Determining the Best N workers per department

The example demonstrates the usage of the DENSE_RANK() to determine the top N employees per department by salary. How about getting the best 2 employees in every department. 

1. WITH RankedEmployees AS (  
2.     SELECT  
3.         EmployeeName,Department,Salary  
4.         ,DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DepartmentSalaryRank  
5.     FROM  Employees  
6. )  
7. SELECT  
8.     EmployeeName,Department,Salary  
9. FROM     RankedEmployees  
10. WHERE DepartmentSalaryRank <= 2;  

 

1. --output  
2. EmployeeName                   Department                     Salary  
3. ------------------------------ ------------------------------ ------------------------------  
4. David Lee                      IT                             90000.00  
5. Ivy Taylor                     IT                             90000.00  
6. Frank Miller                   IT                             80000.00  
7. Eve Wilson                     Marketing                      75000.00  
8. Bob Johnson                    Marketing                      75000.00  
9. Henry Moore                    Marketing                      70000.00  
10. Alice Smith                    Sales                          60000.00  
11. Charlie Brown                  Sales                          60000.00  
12. Grace Davis                    Sales                          55000.00  
13. Jack White                     Sales                          55000.00  

 

The highest compensation rankings in each department are shown in these tables. Relationships are also included (for example, Alice and Charlie both earn the same amount in sales and are therefore ranked 1).Overall, you will have the highest and second-highest salaries in each department.

DENSE_RANK() vs. RANK() vs. ROW_NUMBER()

Each of them has its own mechanism of operation. It is only good to know the difference so that you can choose the right one to be used.

• DENSE_RANK() fills in the numbers in gaps without breaks in whole numbers even in the event of ties.
• RANK() assigns rank sequentially without leaving gaps as well, except that it omits a rank in the event of a tie. When two rows contain the identical value, they will be assigned identical ranks and the successive rank will be skipped.
• Row_number() returns a sequential integer to each row regardless of whether there are or are not tied rows. It is helpful when required to have a unique series of the rows and no spaces.

1. SELECT    
2.     EmployeeName,    
3.     Salary,    
4.     DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank,    
5.     RANK() OVER (ORDER BY Salary DESC) AS Rank,    
6.     ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber    
7. FROM    
8.     Employees    
9. ORDER BY Salary DESC;    

 

1. --output  
2.   
3. EmployeeName                   Salary                         DenseRank            Rank                 RowNumber  
4. ------------------------------ ------------------------------ -------------------- -------------------- --------------------  
5. David Lee                      90000.00                       1                    1                    1  
6. Ivy Taylor                     90000.00                       1                    1                    2  
7. Frank Miller                   80000.00                       2                    3                    3  
8. Eve Wilson                     75000.00                       3                    4                    4  
9. Bob Johnson                    75000.00                       3                    4                    5  
10. Henry Moore                    70000.00                       4                    6                    6  
11. Alice Smith                    60000.00                       5                    7                    7  
12. Charlie Brown                  60000.00                       5                    7                    8  
13. Grace Davis                    55000.00                       6                    9                    9  
14. Jack White                     55000.00                       6                    9                    10  

 DenseRank provides non-sparse ranking.

Example: David and Ivy both have income 90,000 -> rank1 both. Rank 2 is the next (rank prior) (no skipped rank) Frank.

Rank assigns an equal rank to ties, and omits the subsequent ranks.

Example: Both David and Ivy are rank 1; but Frank is rank 3 (rank 2 is skipped).

RowNumber gives each row a sequential unique number without consideration of a tie.

Ex. David = 1, Ivy = 2, Frank = 3, etc.

Notice the differences:

• DENSE_RANK() assigns a duplicate ranking to all the employees with the identical salary. The rank above is increased by 1.
• RANK() does this as well but the following rank skips the row that was skipped.
• ROW_NUMBER () assigns the employees different numbers even those with the same salary.  

Use Cases for DENSE_RANK()

DENSE_RANK() comes in useful when you wish to:

• Compute the greatest N items in a category but do not leave any blank positions in case of any ties. As an example, you may display the top 3 best-paid employees by department.
• Calculate percentiles/quartiles.
• Create reports with rankings with no gaps.
• Create dashboards in the form of leaderboards.

Considerations

• Performance: Window functions such as DENSE_RANK() could be costly, particularly with big amounts of data, so ensure queries are optimized.
• Types of data: Depending on the type of data of the column related to the ordering, it may influence the ranking result. Select the type that suits comparisons that you require.
• Null values: The treatment of null values in the ordering column may vary between database systems. Find out how they are handled by your system.
• Ties: In cases that are tied in the ordering column, DENSE_RANK() returns the same ranking to all rows that are tied. Consider the way your application must treat such situations.

Conclusion:

 

DENSE_RANK() is useful in that it enables one to assign ranks to rows in a result set with no gaps in between assignments, even when ties occur. It is essential to know its syntax, its application, and its distinctions as compared to other ranking functions when constructing effective and precise SQL queries. With proper attention to the use cases and possible considerations, you can use the DENSE_RANK() to address problems with ranks effectively in many different ways.