Oracle: limiting the number of records (like TOP n, LIMIT)

Fetching a limited number of rows from Oracle is much harder than SQL Server or MySQL. This article explains how to do it using a subquery and the row_number function

 

In Microsoft SQL Server, you can use the keyword TOP to restrict the number of rows returned by your query, like this:

SELECT TOP 10, * FROM Staff ORDER BY EmploymentStartDate;

In MySQL you can use the limit keyword:

SELECT * FROM Staff ORDER BY EmploymentStartDate LIMIT 10;

With Oracle it isn’t so simple. At first glance, it looks as if the ROWNUM virtual column might do the trick, but sadly it doesn’t. ROWNUM is the actual position of a row in the physical database on disk, so it doesn’t correlate with our ORDER.

There is an alternative to ROWNUM that looks like it might work: row_number().
row_number is a ‘window’ function. It looks at the data after the where clause is
applied, and is applied in an order that we specify using the keyword OVER. Unfortunately,
because it happens after the WHERE is evaluated, it makes it impossible to use for our
purpose of only selecting a certain number of rows. The following code won’t work:

SELECT *, row_number() over (ORDER BY published_date desc) AS rownumber 
    FROM Staff WHERE rownumber <= 10 
    ORDER BY EmploymentStartDate;

Instead, to use row_number() to filter the number of rows returns, we must use a subquery. This is
because the sub-query is executed before the outer query – the
rownumber has been generated and is available to the WHERE clause.

Here is the working query, implemented as a sub-query on an inner join. Note
that we need to join the ordered table to the original table, so we have to select
a unique key (usually the primary key) along with the row_number:

SELECT s.*, staff_order.rownumber
    FROM Staff s
    INNER JOIN (SELECT StaffId, row_number() AS rownumber
        over (ORDER BY EmploymentStartDate desc) AS rownumber FROM Staff) staff_order
    WHERE staff_order.rownumber <= 10
    ORDER BY staff_order.rownumber;

The above code will select the 10 most recent employees. Note the over() in the subquery (this applies the
sort order of row_number). Note also that we select rownumber in the main select, that we use the where clause
to limit the number of rows (acheiving the equivalent of TOP 10), and that we order the main record set by
rownumber.

If you want to restrict the results (e.g. WHERE first name is something or other), you must add the WHERE to the sub-query. The outer query will automatically be filtered because we are INNER JOINing the sub-query on StaffId, so only matching StaffIds are there to be joined on.

This is far more complicated than with other database systems, but once you
get it working it works well. Sub-queries are less than ideal from a code-maintenance point of view, however, so here
is the same code written using WITH:

WITH staff_order AS (
    SELECT StaffId, row_number() over (ORDER BY EmploymentStartDate desc) AS rownumber
        FROM Staff
        WHERE Status = 1
)
SELECT s.*
    FROM Staff s
    INNER JOIN staff_order so
        ON s.StaffId = so.StaffId
    WHERE so.rownumber <= 10
    ORDER BY so.rownumber;

I hope you find this code useful!