The DENSE RANK () Function is another ranking function that is used to rank the set of rows. This DENSE RANK () function assigns a unique rank to each row and the rank will reset with each partition that is defined by the PARTITION BY( ) clause. Unlike the RANK Function, the rank will not be skipped if two records have the same value. The next ranking value will be assigned to the following rows.
Demo Table –
Product Table
| Category | Region | City | Country | Sales | Customer_name |
| Office Supplies | Central | Chicago | United States | 15.696 | Suzanne McNair |
| Technology | East | New York City | United States | 539.97 | Amy Hunt |
| Office Supplies | East | New York City | United States | 22.58 | Amy Hunt |
| Office Supplies | East | Dover | United States | 16.52 | Emily Phan |
| Office Supplies | East | Dover | United States | 671.94 | Emily Phan |
| Technology | West | Aurora | United States | 169.064 | Tracy Poddar |
| Office Supplies | West | Aurora | United States | 168.624 | Tracy Poddar |
| Office Supplies | East | Long Beach | United States | 16.52 | Anthony Rawles |
| Office Supplies | West | Pasadena | United States | 31.92 | Helen Andreada |
| Furniture | West | Pasadena | United States | 433.568 | Helen Andreada |
Example – Consider the above Product table of the Customer database and evaluate the rank of the customers using the Sales column.
Select Category,Region,City,[Country/Region],[Customer Name],Sales, dense_rank() OVER(PARTITION BY CATEGORY ORDER BY SALES DESC) AS D_R FROM [dbo].[Products];
This output is the small section of the output by executing the above query –
| Category | Region | City | Country | Customer_name | Sales | D_R |
| Furniture | West | Pasadena | United States | Helen Andreada | 433.568 | 1 |
| Office Supplies | East | Dover | United States | Emily Phan | 671.94 | 1 |
| Office Supplies | West | Aurora | United States | Tracy Poddar | 168.624 | 2 |
| Office Supplies | West | Pasadena | United States | Helen Andreada | 31.92 | 3 |
| Office Supplies | East | New York City | United States | Amy Hunt | 22.58 | 4 |
| Office Supplies | East | Long Beach | United States | Anthony Rawles | 16.52 | 5 |
| Office Supplies | East | Dover | United States | Emily Phan | 16.52 | 5 |
| Office Supplies | Central | Chicago | United States | Suzanne McNair | 15.696 | 6 |
| Technology | East | New York City | United States | Amy Hunt | 539.97 | 1 |
| Technology | West | Aurora | United States | Tracy Poddar | 169.064 | 2 |
As in the above results, for office categories where it is a tie of sales between Anthony Rawles and Emily Phan, the next rank value is incremented by 6 for Suzanne McNair and no values are skipped.
Other SQL topics to check out:
- What is RDBMS (Relational Database Management System)?
- SQL SELECT Statement
- SQL INSERT INTO SELECT Statement
- SQL ALL Operator
- SQL WHERE Clause
- SQL AND Operator
- SQL OR Operator
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
