RANK Function

The RANK function in Oracle is an analytic function used to assign a unique rank to each row within a result set, based on the specified ordering of the rows. The rank is calculated for each row based on the specified ORDER BY clause, and rows with identical values in the ordering columns are assigned the same rank, but with gaps between ranks for subsequent rows.

 

1. What is the RANK Function?

The RANK function is used to assign a rank to each row in a result set based on the specified column(s) in the ORDER BY clause. It is often used for generating rankings (e.g., top-performing employees, sales rankings) or for scenarios where you need to assign sequential numbers to rows.

• The ranking starts from 1.
• Rows with the same value (or tie) in the ORDER BY column(s) receive the same rank.
• If two rows share the same rank, the next rank is skipped (i.e., there is a gap).

 

2. Basic Syntax

The basic syntax for the RANK function is as follows:

SELECT column1, column2, RANK() OVER (ORDER BY column_name) AS rank

FROM table_name;

• RANK() is the function.
• OVER clause is mandatory to specify how the ranking is calculated.
• ORDER BY column_name determines the ranking order based on the specified column.
• AS rank is the alias name for the computed rank column.

 

3. How RANK Works

• Ranking: The first row gets the rank 1, and the subsequent rows are assigned incrementally.
• Tied Ranks: If multiple rows have the same value in the ordered column, they share the same rank, but the next rank is skipped.
• Example: If two rows have the same value and are ranked 1, the next rank will be 3 (not 2), leaving a gap.

Example:

Assume the following table of sales data:

Salesperson	Sales Amount
Alice	1000
Bob	1500
Carol	1500
Dave	1200

Using the following query:

SELECT Salesperson, Sales_Amount,

       RANK() OVER (ORDER BY Sales_Amount DESC) AS Rank

FROM Sales;

The result will be:

Salesperson	Sales Amount	Rank
Bob	1500	1
Carol	1500	1
Dave	1200	3
Alice	1000	4

• Bob and Carol share the 1st rank (tie).
• The next rank assigned is 3 (skipping rank 2), which is given to Dave.
• Alice gets rank 4.

 

4. RANK vs DENSE_RANK vs ROW_NUMBER

The RANK function behaves differently from other similar ranking functions in Oracle, such as DENSE_RANK and ROW_NUMBER. Here's how they compare:

Function	Behavior
RANK	Assigns gaps in the rank if there are ties.
DENSE_RANK	Does not assign gaps in ranking when there are ties.
ROW_NUMBER	Assigns a unique row number to each row, regardless of ties.

Example:

For the same Sales data as above:

SELECT Salesperson, Sales_Amount,

       RANK() OVER (ORDER BY Sales_Amount DESC) AS RANK,

       DENSE_RANK() OVER (ORDER BY Sales_Amount DESC) AS DENSE_RANK,

       ROW_NUMBER() OVER (ORDER BY Sales_Amount DESC) AS ROW_NUM

FROM Sales;

Salesperson	Sales Amount	RANK	DENSE_RANK	ROW_NUM
Bob	1500	1	1	1
Carol	1500	1	1	2
Dave	1200	3	2	3
Alice	1000	4	3	4

• RANK: Bob and Carol share rank 1, and Dave is ranked 3 (gap).
• DENSE_RANK: Bob and Carol share rank 1, and Dave is ranked 2 (no gap).
• ROW_NUMBER: Every row gets a unique number regardless of ties.

 

5. Using PARTITION BY with RANK

The PARTITION BY clause can be used with the RANK function to restart the ranking for different groups of data (e.g., separate rankings for different departments, regions, or categories).

Syntax:

SELECT column1, column2,

       RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS rank

FROM table_name;

Example:

If you have a sales table with salespersons in different regions, you can partition the ranking by region:

SELECT Salesperson, Region, Sales_Amount,

       RANK() OVER (PARTITION BY Region ORDER BY Sales_Amount DESC) AS Rank

FROM Sales;

This would reset the rank for each region, so the top salesperson in each region gets rank 1.

 

6. Use Cases of RANK Function

• Leaderboard: Ranking employees, products, or salespeople based on performance metrics.
• Sales/Revenue Rankings: For monthly, quarterly, or annual sales data.
• Top N Reports: Finding the top N items or entities based on specific criteria, such as top 3 salespeople, top 5 products, etc.
• Tied Rankings: When there are ties in rankings, and you need to handle them properly (e.g., for contest results or prize distributions).

 

7. Performance Considerations

• Indexes: To optimize performance when using the RANK function, especially in large datasets, consider creating indexes on the columns used in the ORDER BY clause, as the function requires sorting.
• Partitioning: When using PARTITION BY, ensure that the data is appropriately partitioned to avoid performance degradation, particularly for large tables.

 

8. RANK with Date Ranges

The RANK function can also be useful in scenarios where data spans time periods, and you want to rank results within specific time frames (e.g., monthly or quarterly rankings).

Example:

To rank sales by month:

SELECT Salesperson, TO_CHAR(Sales_Date, 'YYYY-MM') AS Month,

       Sales_Amount,

       RANK() OVER (PARTITION BY TO_CHAR(Sales_Date, 'YYYY-MM') ORDER BY Sales_Amount DESC) AS Rank

FROM Sales;

This ranks salespeople within each month.

 

9. Handling NULL Values in RANK

• In Oracle, NULL values are handled as the lowest possible values in an ORDER BY clause by default. If NULL values appear in the column used for ranking, they will be ranked last unless specified otherwise.

 

10. Examples of RANK Usage

Example 1: Ranking Employees by Salary (Without PARTITION)

SELECT Employee_ID, Name, Salary,

       RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank

FROM Employees;

This query ranks employees based on their salary, from highest to lowest.

Example 2: Ranking Employees by Salary by Department (With PARTITION)

SELECT Department_ID, Employee_ID, Name, Salary,

       RANK() OVER (PARTITION BY Department_ID ORDER BY Salary DESC) AS Salary_Rank

FROM Employees;

This query ranks employees within each department based on salary.

11. Conclusion

The RANK function is an essential tool for generating rankings in SQL queries, particularly useful in scenarios involving performance tracking, competition, or any application where a relative rank is needed for rows. By understanding how it handles ties, partitions, and ordering, you can effectively use it to calculate ranks and create complex reports with ranking logic.