The RANK() function assigns a unique rank for each row in the result set. If two records have the same value then it will assign the same rank by skipping the next rank in the sequence. The ranking is done for each row in the partition window. The RANK () Function can easily be operated with or without the PARTITION BY ().
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 | 16.52 | 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, rank() OVER(PARTITION BY CATEGORY ORDER BY SALES DESC) AS Rank FROM [dbo].[Products];
This output is the small section of the output by executing the above query –
Category | Region | City | Country | Customer_name | Sales | Rank |
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 | 7 |
Technology | East | New York City | United States | Amy Hunt | 539.97 | 1 |
Technology | West | Aurora | United States | Tracy Poddar | 169.064 | 2 |
Here, it can be seen the values are been incremented, whereas when the Category changes the incrementation starts over. As for office categories where it is a tie of sales between Anthony Rawles and Emily Phan, the next rank value is incremented by 7 for Suzanne McNair where it skipped 6 for the rank.
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.