How To Use Rank Function in Oracle: 5 Real-World Examples

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 oracle rank function.

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:

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:

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

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:

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 Oracle RANK function:

Sample Data:

Let’s consider student marks to get the highest mark in student name using rank function in oracle:

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:

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 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.

Ref: https://docs.oracle.com