SQL DENSE RANK( ) Function

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:

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.