How to use Rank and Dense Rank in SQL

# How to use Rank and Dense Rank in SQL

Vikram Singh
Assistant Manager - Content
Updated on Oct 3, 2023 11:52 IST

Rank and Dense Rank Functions are the window function used to order values and assign them some number depending on whether they fall in relation to one another. In this article, we will learn how to use rank, dense rank and row number function in sql.

Let you have a sales dataset, and you have to find the top 20 performing assets, or you have to find the best-performing salesman. Then how will you find that?

Method -1: You could arrange and analyze the data manually; that will not be possible if you have millions of records.

But if you are using SQL, you can sort these data in a few seconds by writing 2-3 lines of queries.

Method-2: Using Rank and Dense Rank Function.

Rank and Dense Rank Functions are the window function used to order values and assign them some number depending on whether they fall in relation to one another.

In this article, we will learn how to rank function, dense rank function, how to use them, and the difference between them. So, without further delay, let’s learn more about rank and dense rank functions.

Must Check: SQL Tutorial

## What is Rank Function

The rank function in SQL assigns the rank based on the defined attributes to each row within the given partition. It works with an over clause.

• Ranks in the rank function are determined using the order by clause.
• Ranks in the rank function are given as
• If two rows have identical numbers, they have the same rank.
• The rank of the next unidentical number (row) = Rank assigned to the last row + Number of Duplicates.

Now, let’s take an example to get a more understanding of the row function.

Example

The above table (employee) contains 5 columns, now we want to give ranks to the employees depending on their salaries in increasing order.

So, the output will be:

Hope, from the above example you have a clear understanding of how rank function works.

### Syntax

` `
`SELECT column_nameRANK () OVER ( PARTITION BY column_name ORDER BY column_name [ASC|DESC])FROM table_name;Copy code`

Now, let’s see how to implement the above result using SQL.

### Example

` `
`SELECT * RANK() OVER ( ORDER BY CTC DESC)FROM employee;Copy code`

The above query will produce the same result as above.

Example – 2: Give the rank to employees’ salaries within their departments.

` `
`SELECT * RANK() OVER( PARTITION BY Department ORDER BY CTC)FROM Employee;Copy code`

Output

Now, let’s move to learn how to use the dense rank function.

## Dense Rank Function

A dense rank function is similar to the rank function, but with a single difference, it produces the rank without any kind of gap.

If two or more rows have the same value, then:

• They have been assigned the same rank.
• The rank of the next row will be just increased by 1.

Note: It differs from the rank function, as it generates consecutive rank values.

Confused!!

Let’s take an example to clear your doubts.

Example: Find the dense rank of the salaries of employees from the above table.

### Syntax

` `
`SELECT column_nameDENSE_RANK() OVER( PARTITION BY column_name ORDER BY column_name)FROM table_name;Copy code`

### Example

` `
`SELECT * DENSE_RANK() OVER ( ORDER BY CTC DESC)FROM employee;Copy code`

The above query will produce the same result.

Apart from these two rank functions, we have another function to find the rank in SQL, i.e., row number

## What isRow Number

It defines the unique rank to each row of the table, i.e., if two rows have the same rank, then dissimilar to rank and dense_rank, it will give a distinct number to both rows.
Let’s take an example where we use all three: rank, dense_rank, and row_number.

Example: Find the rank, dense rank, and row number of CTC in the above employee table.

` `
`SELECT * RANK () OVER (ORDER BY CTC DESC) DENSE RANK () OVER (ORDER BY CTC DESC) ROW_NUMBER () OVER (ORDER BY CTC DESC)FROM employee;Copy code`

Output

## Conclusion

In this article, we have briefly discussed rank, dense_rank, and row_number functions of sql and how to use them.

Hope you will like the article.