RANK vs DENSE_RANK vs ROW_NUMBER: When to Use Each

INTRODUCTION:

RANK, DENSE_RANK, and ROW_NUMBER are commonly used window functions in SQL (Structured Query Language).

Lets breakdown all terms.

What is SQL(Structured Query Language)?

SQL is the standard language used to talk to relational databases. 

Let’s take the real life example Library in the whole blog. 

For example if you want to talk to the librarian to ask a doubt or question you should use any language like Telugu, Hindi or English, in the same way relational databases also needs some language to understand and also it should be with proper structure. If you are talking to him in Hindi but he doesn’t know Hindi he won’t understand it, in the same way the databases won’t understand and it throws error when there is no syntax or  structure.

What is Database?

Database is a place where data is stored in organized way so that it can be fetched and used easily. In the same Library example, if you don’t organize the books properly you won’t be able to find the book easily. Here the library is database and books are data.

What is relational database?

Relational database is a type of database where the data is stored in rows and columns I.e. tables. In the Library example you can consider racks and shelves that stores books.

SQL is the standard language. We have many database management systems that uses SQL like MySQL, SQL Server, PostgreSQL, Oracle etc. You can consider these as different Library systems across the city. Every Library system has its own set of rules called syntax and own structure to organize the data. But all the DBMS(Database Management Systems) has almost similar structure which is SQL with slight differences in the syntax.

What does this blog solve?

It explains the difference between RANK, DENSE_RANK, and ROW_NUMBER, and helps you decide when to use which.

1.DENSE_RANK():

DENSE_RANK is function used to give rank(position) to rows based on some order. Like we give ranks in our schools or competitions. This function does not skip ranks when there are ties.

Syntax:

DENSE_RANK() OVER (PARTITION BY column1, column2, ... ORDER BY sort_column1, sort_column2, ...)

• DENSE_RANK(): The function itself. 

• PARTITION BY: This optional clause divides the result set into partitions or groups, and the ranking is applied within each partition separately. If omitted, the entire result set is treated as a single partition. ORDER BY: This clause specifies the columns by which the data is sorted to determine the ranking order.

Example:

Requirement: You want to fetch the top 3 borrowed books in the library.

Table Name: Library

BOOK_NAME	BORROWED_COUNT
Book A	5
Book B	5
Book C	4
Book D	2

SELECT *,DENSE_RANK() OVER(ORDER BY BORROWED_COUNT DESC) AS DENSE_RANK FROM  LIBRARY 

If we use DENSE_RANK() function you will get the following output:

BOOK_NAME	BORROWED_COUNT	DENSE_RANK
Book A	5	1
Book B	5	1
Book C	4	2
Book D	2	3

Here there is tie for Book A and Book B those are the most borrowed books so both should be ranked 1. Even though Book C is the third row, it gets rank 2 because it is the second highest borrowed book. No ranks are skipped.

2.RANK():

RANK is also a function used to give rank(position) to rows based on some order. Like we give ranks in our schools or competitions. This function skips ranks when there are ties.

Syntax:

RANK() OVER (PARTITION BY column1, column2, ... ORDER BY sort_column1, sort_column2, ...)

Parameters 

• RANK(): The function itself. 

• PARTITION BY: An optional clause that divides the result set into partitions or groups. The ranking is applied within each partition separately. If omitted, the entire result set is treated as a single partition. 

• ORDER BY: This clause specifies the columns by which the data is sorted to determine the ranking order

Example:

Requirement: You want to fetch the top 3 borrowed books in the library.

Table Name: Library

BOOK_NAME	BORROWED_COUNT
Book A	5
Book B	5
Book C	4
Book D	2

SELECT *,RANK() OVER(ORDER BY BORROWED_COUNT DESC) AS DENSE_RANK FROM  LIBRARY 

If we use RANK() function you will get the following output:

BOOK_NAME	BORROWED_COUNT	RANK
Book A	5	1
Book B	5	1
Book C	4	3
Book D	2	4

For the same example in DENSE_RANK(), the difference here is the rank 2 is skipped because it is occupied by Rank 1

3.ROW_NUMBER()

ROW_NUMBER is a function that assigns sequential unique number based on the order we specify. No ties no skips.

Syntax:

ROW_NUMBER () OVER ( [ PARTITION BY col_1,col_2... ] ORDER BY col_3,col_4.. ASC or DESC) AS column_name

• PARTITION BY: This is the main sub-clause that partitions the rows into windows and for each row, the values of window functions applied will be calculated. 

• ORDER BY: This is used to order the rows in the partition, by default it is the ascending order. Without the ORDER BY clause, the ROW_NUMBER() function doesn't work.

Table Name: Library

BOOK_NAME	BORROWED_COUNT
Book A	5
Book B	5
Book C	4
Book D	3

SELECT *,ROW_NUMBER() OVER(ORDER BY BORROWED_COUNT DESC) AS ROW_NUMBER FROM  LIBRARY 

If we use ROW_NUMBER() function you will get the following output:

BOOK_NAME	BORROWED_COUNT	ROW_NUMBER
Book A	5	1
Book B	5	2
Book C	4	3
Book D	3	4

Bonus:

In this case, Book A and Book B have the same BORROWED_COUNT, but ROW_NUMBER() assigns 1 to Book A and 2 to Book B. This happens because ROW_NUMBER() must assign a unique number to every row even when there is a tie. 

When the column used in the ORDER BY clause has duplicate values, SQL internally decides the order in which the tied rows are processed. This order cannot be determined unless you explicitly specify additional columns in the ORDER BY clause.

WHEN TO USE WHICH?

RANK(): 

1. When continuous ranking is not required

2. You doesn’t care about gaps

3. You want to differentiate properly when there are ties

4. Ex: sports races, leaderboards

DENSE_RANK(): 

1. When continuous ranking is required

2. You care about gaps

3. Ex: exam results

ROW_NUMBER():

1. No ties allowed

2. You need unique sequential numbers even when there are ties

3. Ex: count number of records, top N rows