Oracle: DENSE_RANK() Function

By Atul Rai | Last Updated: August 2, 2023 Previous       Next

The DENSE_RANK() function in Oracle is used to assign a unique rank to each distinct row within a result set based on the specified ordering criteria. It is part of the SQL analytic functions that allow you to perform calculations across a set of rows related to the current row.

The syntax of the DENSE_RANK() function is as follows:

DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression [ASC | DESC])

Let’s break down the components:

• DENSE_RANK(): This is the actual function name.
• OVER: This keyword is used to indicate that the ranking operation should be performed within the specified window frame.
• PARTITION BY: This is an optional clause that divides the result set into partitions or groups based on the values of the specified column(s). The DENSE_RANK() will restart from 1 for each partition. If you omit this clause, the whole result set will be treated as a single partition.
• ORDER BY: This is mandatory and defines the column(s) based on which the ranking is determined. Rows are sorted in the specified order, and the rank is assigned accordingly.
• ASC | DESC: This is an optional clause that specifies whether the sorting should be in ascending (ASC) or descending (DESC) order. If omitted, the default is ascending order.

The DENSE_RANK() function handles ties by assigning the same rank to rows with equal values, and the next rank is incremented accordingly. For example, if two rows have the same values and receive a rank of 1, the next row will be assigned a rank of 3 (in case of no gaps) or 2 (in case of gaps), and so on.

Here’s a simple example to illustrate its use:

Consider a table scores with the following data:

If you want to rank the students based on their scores, you can use the DENSE_RANK() function:

SELECT
  Student,
  Score,
  DENSE_RANK() OVER (ORDER BY Score DESC) AS Rank
FROM
  scores;

The result will be:

As you can see, Alice and Chris have the same score, so they both get rank 1, and Bob gets rank 2. The DENSE_RANK() function provides consecutive rankings without gaps, unlike the RANK() function, which leaves gaps for ties.

References

1. DENSE_RANK()- OracleDoc
2. Top 5 SQL Developer Shortcut Keys
3. Top 5 SQL Developer Shortcut Keys