What is the difference between dense_rank() and rank() in SQL?

What is SQL?

SQL (Structured Query Language) is a programming language used to obtain and manipulate data contained in databases, specifically relational databases. To understand relational databases, read this Answer.

As with any programming language, SQL contains functions designed to help the programmer perform specific tasks.

There are many functions in SQL. However, in this Answer, we shall focus on DENSE_RANK() and RANK(), referred to as Rank functions.

Rank functions assign a rank or a position to a set of rows in a partition.

How to use RANK() in SQL

RANK() is a function that assigns ranks within a specified set of rows or partitions.

RANK() assigns the same rank to rows with ties, for example, (1,2,3,3,3,6,7), and then the next value will be the rank increased by (1/2) depending on the number of ties.

Syntax

SELECT column1, column2, ..., 

RANK() OVER (ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...) as rank_column_name

FROM table_name;

Explanation

  • Line 1: SELECT specifies the columns. RANK() specifies the ranking method. In this case, rows with ties are assigned the same rank.

  • Line 2: OVER is used with RANK() to specify which columns the ranking will be done on. ORDER BY specifies the order of the rows based on the specified column(s), with the option to sort in ascending (ASC) or descending (DESC) order. as provides an alias to the column(s) used for ranking.

  • Line 3: FROM indicates the table name.

How to use DENSE_RANK() in SQL

Dense_rank() is another function similar to RANK(). The DENSE_RANK() function assigns consecutive rank values to each row. If two or more rows have the same rank value, the next rank value is a consecutive number, for example, (1,2,2,3,4,4,4,5).

Syntax

SELECT column1, column2, ..., 

DENSE_RANK() OVER (ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...) as rank_column_name

FROM table_name;

Explanation

The above code has similar elements except the following:

  • Line 2: DENSE_RANK() assigns ranks to rows consecutively, irrespective of ties.

Difference between RANK() and DENSE_RANK() in SQL

Both RANK() and DENSE_RANK() are the same except as they assign the same rank to ties except RANK() assigns the next rank to the rows, such as (1,2,3,3,5) while DENSE_RANK() will assign consecutive numbers to rows after ties, for example (1,2,3,3,4).

Practical application in SQL

SELECT bev_name, price,

RANK() OVER (ORDER BY price DESC) AS price_rank,

DENSE_RANK() OVER (ORDER BY price DESC) AS price_dense_rank

FROM beverage;

Explanation

  • Line 1: The Select function selects the columns, bev_name and price.

  • Line 2: Use RANK() to assign ranks to the beverages using the price column. In this case, the example above assigns water to rank 5.

  • Line 3: Use DENSE_RANK() to assign beverage ranks using the price column. In this case, water is ranked 4 in the above example.

  • Line 4: Specifies the table beverage as the data source.

Conclusion

RANK() and DENSE_RANK() can be used interchangeably depending on our required ranking. For example, if we need consecutive ordering, then DENSE_RANK() is better, but if not, then RANK() can be used.

Note: ROW_NUMBER() is another ranking function in SQL that works similarly to RANK() and DENSE_RANK() with a difference in that the ranking is distinct among all the values, as in (1,2,3,4,5…), even those with ties.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved