The rank function in Oracle is a useful tool that gives a rank on each row in the specific data group filtered according to specific criteria. Hope to explain the rank analytic and aggregate function in Oracle with simple examples.
Additionally, we cover the other essential things like performance and how these WITHIN GROUP, OVER keywords map with Rank Function in Oracle.
There are two ways of RANK() function in Oracle is as follows :
Analytic Syntax of the RANK() Function:
RANK() OVER ( [PARTITION BY partition_expression ] ORDER BY sort_expression)
- PARTITION BY (optional): This is used to break filtered data into separate partitions. Here we add a breaking parameter (column name). each partition has a separate ranking for a group of rows.
- ORDER BY: This is the usual way of ordering data normally used in everywhere in queries. You can use ORDER BY either in ascending (default) or descending order.
- RETURN: NUMERIC value
Aggregate Syntax of the RANK() Function:
-- RANK( parameter_list ) WITHIN GROUP ( ORDER BY sort_expression] ) --
- PARAMETER (optional): This is used to identify the unique rows within the group. Then return the matching row according to the parameter list.
- ORDER BY: This is the usual way of ordering data normally used in everywhere in queries. You can use ORDER BY either in ascending (default) or descending order.
- RETURN: NUMERIC value
I would like to NOTE here we expect non-consecutive ranking values from the group. If the same rows are equal then return the same rank for both rows and the next row return not return the consecutive value as a NUMBER.
- Available on: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Analytic RANK() Function Real Examples
Example 1: Rank Employees by Salary
Sample Data:
Start the examples with the most basic example of employee salary:
Employee_ID | Employee_Name | Salary |
1 | Tom | 55000 |
2 | Bob | 60000 |
3 | Lusy | 60000 |
4 | David | 52000 |
5 | Li | 58000 |
Query:
If you would like to rank employees according to the salary and salary ordered in descending order:
-- SELECT Employee_ID, Employee_Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank FROM employees; --
Result:
Employee_ID | Employee_Name | Salary | Salary_Rank |
2 | Bob | 60000 | 1 |
3 | Lusy | 60000 | 1 |
5 | Li | 58000 | 3 |
1 | Tom | 55000 | 4 |
4 | David | 52000 | 5 |
Here you can see the same rank for Bob and Lusy because both of them get the same salary.
Example 2: Ranking Products by Price within Categories
Sample Data:
In our next example let’s use partition and order by both on ranking. Then we have to have at least a partitioning column and ordering column like below
Product_ID | Product_Name | Category | Price |
1 | Laptop | Electronics | 1000 |
2 | Mobile Phone | Electronics | 800 |
3 | T-Shirt | Apparel | 20 |
4 | Watch | Accessories | 200 |
5 | Trouser | Appeal | 50 |
Query:
Below query rank each item inside the category:
-- SELECT Product_ID, Product_Name, Category, Price, RANK() OVER (PARTITION BY Category ORDER BY Price DESC) AS Price_Rank FROM products; --
Result:
Product_ID | Product_Name | Category | Price | Price_Rank |
1 | Laptop | Electronics | 1000 | 1 |
2 | MobilePhone | Electronics | 800 | 2 |
3 | Trouser | Apparel | 50 | 1 |
4 | T-Shirt | Apparel | 20 | 2 |
5 | Watch | Accessories | 200 | 1 |
You can see items ranked by price but are partitioned by category based on descending prices within each category.
Example 3: Fetch the Highest mark using the Rank Function in Oracle:
Sample Data:
Let’s consider student marks to get the highest mark in student name using rank function in oracle:
Student_ID | Student_Name | Mark |
1 | Alice | 95 |
2 | Bob | 85 |
3 | Carol | 90 |
4 | David | 85 |
5 | Emily | 92 |
Query:
Below query first order the result by mark and get the rank for each student:
-- WITH student_mark AS ( SELECT Student_name, RANK() OVER (ORDER BY Mark DESC) AS Rank_mark FROM student_mark ) SELECT Student_ID ,Student,Mark FROM student_mark WHERE Rank_mark = 1 FETCH FIRST 1 ROWS ONLY; --
Result:
Student_ID | Student_Name | Mark |
1 | Alice | 1 |
An example shows how to get the highest score student. If you would like rank help to get the highest score student by each subject using partition.
Aggregate RANK() Function Real Examples
Example Using RANK() with the WITHIN GROUP Clause:
The WITHIN GROUP clause is useful when filtering records or getting selected rows by user-entered margin.
Think about columns available for salary and commission. The rank parameter passed on filter values salary equals with 15500 and commission equals .05%
-- SELECT RANK(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct) "Rank" FROM employees; --
Bonus Example Using RANK() with Inner Queries:
The RANK() function can be used in combination with subqueries or inner queries
-- SELECT customer_id, total_orders, RANK() OVER (ORDER BY total_orders DESC) AS customer_rank FROM ( SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id ) subquery; --
Performance Considerations:
Nowadays performance is the most critical thing in applications. Because of application performance, it is never going to be usable. Therefore query performance is highly encouraged when writing queries if you would like to see our performance articles in different areas.
1. Indexes:
Indexes are the best way to improve the performance in queries there are a lot of ways to create an index according to dataset and column type. Order can be improved using an index.
2. Data Distribution:
Data load and how they are distributed change the performance of a query. The affected dataset in the query is the most important. Apply condition to query for removing unwanted data from the dataset.
3. Explain plan:
Explain plan is one of the good sources for identifying performance issues. But that is not the only option for identifying the performance.
4. Query Optimization:
Query optimization is a challenging thing when writing queries. As above mention explain plan is a great tool for identifying the flows. Also profiling queries or AWR reports and similar types of reports can help with performance.
Conclusion:
The rank function in Oracle is a powerful function for ranking(non-consecutive ranks) data in a result set. The Rank Function in Oracle can be used as an analytic or aggregate. The function returns the Numeric value and output showing with rank value according to the ascending or descending order.
The RANK function is the rank of each row according to the base data set. I hope simple examples are enough to understand the concept and how to use the oracle rank function.