Oracle: RANK() Function

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

In this blog, we will explore the concept of the RANK() function in the Oracle database. The RANK() function in Oracle is a window function that assigns a unique rank to each row in the result set based on the specified criteria. It provides a straightforward way to rank rows according to their values in one or more columns.

Syntax of the RANK() Function

The syntax for the RANK() function is as follows:

RANK() OVER (PARTITION BY column1, column2, ... ORDER BY column3 [ASC | DESC])

Explanation:

• RANK(): The function name that indicates we want to assign ranks to rows.
• PARTITION BY: It divides the result set into partitions, allowing ranking within specific groups of rows.
• ORDER BY: The column(s) based on which the ranking is determined.
• ASC or DESC: Specifies the order of the ranking. ASC for ascending and DESC for descending.

1. Simple Ranking

Suppose we have a table named “Scores” that contains students’ names and their corresponding scores.

Let’s find the ranks of the students based on their scores in descending order:

SELECT
    student_name,
    score,
    RANK() OVER(
        ORDER BY score DESC
    ) AS rank
FROM
    scores;

Output:

Explanation:

• Charlie gets the highest rank (1) as he has the highest score (92).
• Alice and David both have a score of 90, so they share the rank of 2.
• Emma and Bob follow with ranks 4 and 5, respectively.

2. Ranking within Partitions

Now, let’s consider a table named “Exams” with student scores in two subjects – Math and English:

We want to find the ranks of students based on their scores within each subject:

SELECT
    student_name,
    subject,
    score,
    RANK() OVER(
        PARTITION BY subject
        ORDER BY score DESC
    ) AS rank
FROM
    exams;

Output:

Explanation:

• Charlie ranks first in Math, and Alice ranks first in English.
• Within Math, Charlie holds the top position, while David is second.
• Similarly, within English, Alice is ranked first, and Emma follows at second place.

Summary

The Oracle RANK() function allows us to organize and analyze data based on specific columns, providing useful insights into the distribution of values.

References

1. Oracle: DENSE_RANK() Function
2. RANK- OracleDoc
3. Top 5 SQL Developer Shortcut Keys