Oracle ROW_NUMBER Function

ROW_NUMBER gives each row of a result, or of each partition, a sequential number in the order you choose: 1, 2, 3, with no ties. It is the workhorse of top-N-per-group queries, deduplication, and pagination, and the analytic function you will use most.

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

Syntax:

row_number() over ([partition by expr [, ...]] order by expr [, ...])

PARTITION BY restarts the numbering for each group; ORDER BY decides the order. Rows that tie on the ORDER BY still get different numbers, in an order Oracle chooses.

ROW_NUMBER Compared with RANK and DENSE_RANK

This query numbers customers by their number of bookings with all three ranking functions.

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 tie with 12 bookings. ROW_NUMBER gives them 2 to 7, in no particular order: customer 4 got 7 and customer 49 got 4. Run it again and the order among them may change. Add a tie-breaker, such as ORDER BY count(*) DESC, customer_id, whenever the numbers must be repeatable.

Top N per Group

Numbering each department's employees by salary and keeping the first two gives the two best-paid per department.

Example:

select department_id, last_name, salary
from   (select department_id, last_name, salary,
               row_number() over (partition by department_id order by salary desc) as rn
        from   employees)
where  rn <= 2 and department_id in (20, 30, 40)
order  by department_id, salary desc;

Output:

   DEPARTMENT_ID LAST_NAME       SALARY
________________ ____________ _________
              20 Clarke           27000
              20 Sato             25500
              30 Rahman           22000
              30 Taylor           11800
              40 Evans            34000
              40 Kapoor           21000

6 rows selected.

An inline view is needed because analytic functions cannot be used in WHERE; in Oracle AI Database 26ai, QUALIFY does the same without the subquery.

Keep One Row per Key

Deduplication is top-1 per group: number each customer's bookings from the latest, and keep number 1.

Example:

-- each customer's latest booking: number the bookings per customer, keep number 1
select customer_id, booking_ref, booked_at
from   bookings
where  customer_id in (1, 2, 3)
qualify row_number() over (partition by customer_id
                           order by booked_at desc, booking_id desc) = 1
order  by customer_id;

Output:

   CUSTOMER_ID BOOKING_REF    BOOKED_AT
______________ ______________ _______________________
             1 P2N7JP         23-FEB-2026 18:23:00
             2 QTJ2YH         12-FEB-2026 23:14:00
             3 4B2QFZ         07-MAR-2026 06:27:00

The second ORDER BY column, booking_id, breaks ties between bookings made at the same moment, so the result is deterministic.

Things to Know

• ROW_NUMBER requires ORDER BY in the OVER clause.
• For a simple first N rows of a whole result, FETCH FIRST n ROWS ONLY is simpler.
• To delete duplicates, select their ROWIDs with ROW_NUMBER > 1 and delete those.

Related Guides

• How to Filter Analytic Results with QUALIFY in Oracle
• How to Define Window Frames in Oracle Analytic Queries

Conclusion

ROW_NUMBER numbers the rows of each partition 1, 2, 3 in the order of its ORDER BY, breaking ties arbitrarily. Add a unique tie-breaker for repeatable results, and filter on it, with an inline view or QUALIFY, for top-N per group and deduplication.