The RANK() function is an analytic function in Oracle SQL used to assign a rank to each row within a partition of a result set, based on the ordering of a specified column(s). It is commonly used for ranking results, like top performers, scores, or sales.

Syntax

RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
)

Components:

• PARTITION BY partition_expression (optional): Divides the result set into partitions (like groups). Ranking restarts for each partition.
• ORDER BY order_expression: Determines the ranking order.
• ASC / DESC: Specifies ascending or descending order.

Key Characteristics

1. Duplicate values get the same rank.
2. Ranks are skipped after duplicates (dense ranking is different – see DENSE_RANK()).
3. RANK() is 1-based: The first row starts with 1.
4. Works as an analytic function, meaning it doesn’t collapse rows but adds a column to the result set.

Basic Example

Assume a table employees:

CREATE TABLE employees (
    employee_id NUMBER,
    name VARCHAR2(50),
    department_id NUMBER,
    salary NUMBER
);

INSERT INTO employees VALUES (1, 'Alice', 10, 5000);
INSERT INTO employees VALUES (2, 'Bob', 10, 7000);
INSERT INTO employees VALUES (3, 'Charlie', 20, 6000);
INSERT INTO employees VALUES (4, 'David', 20, 6000);
INSERT INTO employees VALUES (5, 'Eve', 10, 7000);
COMMIT;

Query: Rank employees by salary

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Output:

NAME	SALARY	RANK
Bob	7000	1
Eve	7000	1
Charlie	6000	3
David	6000	3
Alice	5000	5

Explanation:

• Bob and Eve have the highest salary, so both are rank 1.
• The next salary (6000) gets rank 3 (skipping rank 2).
• Alice gets rank 5.

Using PARTITION BY

You can rank within groups, e.g., by department_id:

SELECT department_id, name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Output:

DEPARTMENT_ID	NAME	SALARY	DEPT_RANK
10	Bob	7000	1
10	Eve	7000	1
10	Alice	5000	3
20	Charlie	6000	1
20	David	6000	1

Explanation:

• Ranks reset for each department.
• Within department 10, Bob and Eve share rank 1; Alice is rank 3.
• Within department 20, Charlie and David share rank 1.

Difference Between RANK() and DENSE_RANK()

Feature	RANK()	DENSE_RANK()
Duplicate values	Same rank, skips next rank	Same rank, no gaps
Example ranks	1, 1, 3, 3, 5	1, 1, 2, 2, 3

Query Example:

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Output:

NAME	SALARY	DENSE_RANK
Bob	7000	1
Eve	7000	1
Charlie	6000	2
David	6000	2
Alice	5000	3

Practical Use Cases

1. Top N employees per department:

SELECT *
FROM (
    SELECT department_id, name, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
    FROM employees
)
WHERE dept_rank <= 2;

2. Leaderboard for sales or scores:

SELECT employee_id, name, total_sales,
       RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM sales_team;

3. Tie handling:

• RANK() handles ties by giving the same rank and skipping subsequent ranks.

Notes & Best Practices

• RANK() does not remove duplicates; it assigns ranks based on ordering.
• Always use ORDER BY inside OVER() to define the ranking logic.
• Combine RANK() with subqueries to filter for top N rows.
• For continuous ranking without gaps, use DENSE_RANK() instead.

Conclusion

The RANK() function in Oracle SQL is a powerful analytic function that:

• Helps rank rows based on specific criteria.
• Handles ties gracefully by assigning the same rank.
• Works with partitions to rank within groups.
• Is ideal for reports, leaderboards, and top-N queries.