SQL RANK ( ) Function

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:

Follow us on TwitterFacebookLinkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

Automate data analysis pipeline and create report ready dashboards

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.