In this Oracle Blog Post, We are sharing most asked interview question in Oracle SQL which is difference between Rank and DENSE_RANK in Oracle SQL.

Discussion Topic

• What is RANK() in Oracle SQL
• What is DENSE_RANK() in Oracle SQL
• Differentiate between RANK() and DENSE_RANK() in Oracle SQL.

When working with data in Oracle databases, especially for reporting or analysis, you often need to assign ranks to rows based on certain criteria. Maybe you want to find the top-performing salespeople, the highest-scoring students, or the most frequently ordered products.

Oracle provides powerful window functions for this purpose, with RANK() and DENSE_RANK() being two of the most common.
While they sound similar, their behavior, especially when dealing with tied values, is crucially different. Understanding this difference is key to getting accurate results. Let's dive in and clarify RANK() vs. DENSE_RANK() with a practical example.

The Scenario: Ranking Employee Sales Performance

Imagine we have a table called EmployeeSales that tracks the total sales amount for each employee in the last quarter.

-- Sample Table Structure
CREATE TABLE EmployeeSales (
    EmployeeID NUMBER,
    EmployeeName VARCHAR2(100),
    Region VARCHAR2(50),
    TotalSales NUMBER
);

-- Sample Data
INSERT INTO EmployeeSales VALUES (101, 'Alice', 'North', 95000);
INSERT INTO EmployeeSales VALUES (102, 'Bob', 'North', 80000);
INSERT INTO EmployeeSales VALUES (103, 'Charlie', 'South', 95000); -- Tie with Alice
INSERT INTO EmployeeSales VALUES (104, 'David', 'North', 75000);
INSERT INTO EmployeeSales VALUES (105, 'Eve', 'South', 88000);
INSERT INTO EmployeeSales VALUES (106, 'Frank', 'South', 80000); -- Tie with Bob

Our goal is to rank these employees based on their TotalSales in descending order (highest sales get the best rank).

Using the RANK() Function

The RANK() function assigns a rank to each row within a partition of a result set. If rows have the same value for the ordering columns (a tie), they receive the same rank. However, the next rank assigned will skip ranks based on the number of tied rows.

Syntax of  RANK() Function

RANK() OVER ([PARTITION BY column1, ...] ORDER BY column2 [ASC|DESC], ...)

Let's apply RANK() to our sales data

SQL

SELECT
    EmployeeID,
    EmployeeName,
    Region,
    TotalSales,
    RANK() OVER (ORDER BY TotalSales DESC) as SalesRank
FROM
    EmployeeSales;

Output

EmployeeID	EmployeeName	Region	TotalSales	SalesRank
101	Alice	North	95000	1
103	Charlie	South	95000	1
105	Eve	South	88000	3
102	Bob	North	80000	4
106	Frank	South	80000	4
104	David	North	75000	6

Explanation

• Alice and Charlie both have the highest sales (95000), so they both get Rank = 1.
• Because there were two employees tied for rank 1, the next rank assigned is Rank = 3 (it skips rank 2). Eve gets rank 3.
• Bob and Frank are tied with 80000 sales. They both receive the next available rank, which is Rank = 4.
• Again, since two employees tied for rank 4, the next rank skips one place (rank 5 is skipped). David gets Rank = 6.

Note:-

The gaps in the ranking sequence (2 and 5 are missing). This is characteristic of RANK().

Using the DENSE_RANK() Function

The DENSE_RANK() function also assigns ranks to rows within a partition based on ordering column. Like RANK(), tied rows receive same rank . However, DENSE_RANK() assigns consecutive ranks without any gaps, regardless of ties

Syntax of  DENSE_RANK() Function

DENSE_RANK() OVER ([PARTITION BY column1, ...] ORDER BY column2 [ASC|DESC], ...)

Let's apply DENSE_RANK() to our sales data

SQL

SELECT
    EmployeeID,
    EmployeeName,
    Region,
    TotalSales,
    DENSE_RANK() OVER (ORDER BY TotalSales DESC) as DenseSalesRank
FROM
    EmployeeSales;

Output

EmployeeID	EmployeeName	Region	TotalSales	DenseSalesRank
101	Alice	North	95000	1
103	Charlie	South	95000	1
105	Eve	South	88000	2
102	Bob	North	80000	3
106	Frank	South	80000	3
104	David	North	75000	4

Explanation

• Alice and Charlie both have the highest sales (95000), so they both get DenseRank = 1.
• Unlike RANK(), DENSE_RANK() assigns the very next rank to the next distinct sales amount. Eve (88000) gets DenseRank = 2.
• Bob and Frank (80000) are tied for the next rank, so they both get DenseRank = 3.
• David (75000) gets the next consecutive rank, DenseRank = 4.

Note:-

The DenseSalesRank column has no gaps; the ranks are consecutive (1, 2, 3, 4).

Difference Between RANK() and DENSE_RANK() in Oracle SQL

1. Handling Ties:

• RANK: Assigns the same rank to tied rows. The subsequent rank number reflects the total number of preceding rows (including all tied rows), causing gaps in the sequence.
• DENSE_RANK: Assigns the same rank to tied rows. The subsequent rank number is always the next consecutive integer, ensuring no gaps in the sequence.

2. Rank Sequence:

• RANK: Can produce a non-consecutive sequence of ranks if ties exist (e.g., 1, 1, 3, 4, 4, 6).
• DENSE_RANK: Always produces a consecutive sequence of ranks (e.g., 1, 1, 2, 3, 3, 4).

3. Use Cases:

• RANK: Often used in competitive scenarios where the rank reflects the absolute position, including ties (like Olympic medal standings where two golds mean no silver is awarded immediately after). Useful when you need to know exactly how many rows are ranked above a given row.
• DENSE_RANK: Ideal when you need a gapless ranking, often used for finding the "Nth" distinct value (e.g., find the employees with the 3rd highest distinct salary level). Useful when the exact count of preceding rows is less important than having a sequential rank identifier for each distinct value group.

Conclusion

Both RANK() and DENSE_RANK() are invaluable tools in your Oracle SQL toolkit for ordering and analyzing data. The choice between them depends entirely on how you want to handle ties and whether you require a consecutive ranking sequence. By understanding their distinct behaviors, you can ensure your queries produce meaningful and accurate results for your specific analytical needs.