In Oracle, the RANK() function is a window function that assigns a unique rank to each row within a result set based on the specified ordering criteria. It is commonly used to determine the rank of values in a dataset, such as ranking employees by salary or ranking products by sales.
Here's the basic syntax of the RANK() function in Oracle:-
RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
- partition_expression: Defines the partitioning criteria for the window. Rows with the same values in this expression will be assigned ranks within each partition.
- order_expression: Specifies the order in which rows are ranked within each partition. The row with the lowest value of this expression gets a rank of 1, the next lowest gets 2, and so on.
Example usage of the RANK() function in Oracle:-
SELECT
employee_id,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_department
FROM
employees;
In this example:
- We partition the data by department_id.
- Within each department, rows are ordered by `salary` in descending order.
- The RANK() function assigns a unique rank to each row within its partition based on the specified order.
- The result includes the employee_id, department_id, salary, and the rank_within_department column indicating the rank of each employee's salary within their department.
This allows you to easily determine, for example, the highest-paid employees within each department.
Let's say we have a table named "employees" with columns "employee_id", "department_id", and "salary". We want to use the RANK() function to rank employees within each department based on their salary. Here's how you can do it:-
CREATE TABLE employees (
employee_id INT,
department_id INT,
salary NUMERIC
);
INSERT INTO employees (employee_id, department_id, salary) VALUES
(1, 101, 50000),
(2, 102, 60000),
(3, 101, 55000),
(4, 103, 48000),
(5, 102, 62000),
(6, 101, 51000);
Now, let's use the RANK() function to rank employees within each department based on salary:-
SELECT
employee_id,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_department
FROM
employees;
Output:
employee_id | department_id | salary | rank_within_department
-------------+---------------+--------+------------------------
5 | 102 | 62000 | 1
2 | 102 | 60000 | 2
3 | 101 | 55000 | 1
6 | 101 | 51000 | 2
1 | 101 | 50000 | 3
4 | 103 | 48000 | 1
(6 rows)
In this output:
- Employees within each department are ranked based on their salary in descending order.
- For example, in department 102, employee with employee_id 5 has the highest salary and is ranked 1st, while employee with employee_id 2 has the second-highest salary and is ranked 2nd.
Here's a simpler example:-
Consider a table named "scores" with columns "student_name" and "score". We want to rank students based on their scores.
Table: scores
| student_name | score |
|--------------|-------|
| Alice | 85 |
| Bob | 92 |
| Charlie | 78 |
| David | 85 |
| Emma | 90 |
Now, let's use the RANK() function to rank students based on their scores:-
SELECT
student_name,
score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM
scores;
Output:
| student_name | score | rank |
|--------------|-------|------|
| Bob | 92 | 1 |
| Emma | 90 | 2 |
| Alice | 85 | 3 |
| David | 85 | 3 |
| Charlie | 78 | 5 |
In this output:-
- Students are ranked based on their scores in descending order.
- Bob has the highest score (92) and is ranked 1st.
- Emma has the second-highest score (90) and is ranked 2nd.
- Alice and David both have a score of 85, so they share the 3rd rank.
- Charlie has the lowest score (78) and is ranked 5th.
Note:-
In the above example, you may notice that there is no rank 4. This is because the RANK() function assigns the same rank to rows with equal values, and then skips the subsequent ranks. In this case, since two students (Alice and David) have the same score and are both ranked 3rd, the next available rank is 5.
Here are five frequently asked questions (FAQs) about the RANK() function in Oracle:-
1. What is the purpose of the RANK() function in Oracle?
The RANK() function assigns a unique rank to each row within a result set based on the specified ordering criteria. It is commonly used for ranking values in a dataset, such as ranking employees by salary or products by sales.
2. How does the RANK() function differ from other ranking functions in Oracle?
Unlike other ranking functions like DENSE_RANK() and ROW_NUMBER(), the RANK() function may produce the same rank for multiple rows if they have equal values according to the specified order. It leaves gaps in the ranking sequence when two or more rows have the same rank.
3. Can I use multiple columns with the RANK() function in Oracle?
Yes, you can specify multiple columns in the PARTITION BY and ORDER BY clauses of the RANK() function. This allows for more granular control over the partitioning and ordering of rows when calculating ranks.
4. Are there any performance considerations when using the RANK() function in Oracle?
Performance may be impacted by the size of the dataset and the complexity of the ordering criteria. It's essential to optimize queries that utilize window functions like RANK() to ensure efficient execution, especially on large datasets.
5. In what scenarios is the RANK() function commonly used in Oracle?
The RANK() function is commonly used in various analytical and reporting scenarios, such as identifying top performers, determining relative rankings based on certain criteria, and generating leaderboard-style lists based on performance metrics.
