In a sports table, two teams level on points share a place, and the next team is placed after both of them: first, second, second, fourth. RANK numbers rows the same way: tied rows get the same rank, and the ranks after them skip as many numbers as there were ties.
Code for This Guide
The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
rank() over ([partition by expr [, ...]] order by expr [, ...])
The rank of a row is 1 plus the number of rows that come before it in the order. Rows with equal ORDER BY values have equal ranks.
Ties and Gaps
Example:
select customer_id, count(*) as bookings,
row_number() over (order by count(*) desc) as row_number,
rank() over (order by count(*) desc) as rank,
dense_rank() over (order by count(*) desc) as dense_rank
from bookings
group by customer_id
order by bookings desc, customer_id
fetch first 9 rows only;Output:
CUSTOMER_ID BOOKINGS ROW_NUMBER RANK DENSE_RANK
______________ ___________ _____________ _______ _____________
5 15 1 1 1
1 12 2 2 2
3 12 3 2 2
4 12 7 2 2
18 12 5 2 2
21 12 6 2 2
49 12 4 2 2
11 11 11 8 3
13 11 10 8 3
9 rows selected.Six customers with 12 bookings share rank 2. The next customers, with 11 bookings, get rank 8: positions 2 to 7 are taken by the tie. ROW_NUMBER, in contrast, gives every row its own number, and DENSE_RANK continues with 3.
Rank within Groups
With PARTITION BY, the ranking restarts for each group, here each department's salaries.
Example:
-- salary rank within each department, with ties
select department_id, last_name, salary,
rank() over (partition by department_id order by salary desc) as rank_in_dept
from employees
where department_id in (50, 70)
order by department_id, rank_in_dept;Output:
DEPARTMENT_ID LAST_NAME SALARY RANK_IN_DEPT
________________ ____________ _________ _______________
50 Mehta 33000 1
50 Reddy 15500 2
50 Williams 8800 3
50 Schmidt 8300 4
70 Fernández 23000 1
70 Li 12500 2
70 Suzuki 11900 3
7 rows selected.Which Ranking Function
| Function | Ties | After a tie of two at 2 |
|---|---|---|
| ROW_NUMBER | Different numbers | 1, 2, 3, 4 |
| RANK | Same rank, then a gap | 1, 2, 2, 4 |
| DENSE_RANK | Same rank, no gap | 1, 2, 2, 3 |
Things to Know
- Filtering on RANK <= 3 can return more than three rows when there are ties, which is often what a "top 3" should do.
- RANK(value) WITHIN GROUP (ORDER BY ...) is a different, aggregate form: it returns where a hypothetical value would rank.
- NULLs sort last in ascending order by default; add NULLS FIRST or NULLS LAST to control their rank.
Related Guides
Conclusion
RANK numbers rows in order, giving ties the same rank and skipping the following numbers, like places in a competition. Use it when ties should share a place and the next place should reflect how many rows came before.
