How to Limit the Number of Rows in Oracle SQL After Ordering

Have you ever needed to select the top N rows in your Oracle query? Or select from rows X to Y? In this article, I’ll show you how you can limit the number of rows in Oracle SQL.

Table of Contents

Selecting the Top Rows

So, in this article, I’ll explain how to select the top rows and to limit the number of rows in Oracle SQL

Why would you want to do this?

You may be using a search function in your code, and want to only show part of the entire result set. If your query returns hundreds of rows, but you only want to show 10 at a time, you would need a query like this.

Or, if you’re doing some kind of pagination, you’ll need to limit the number of records that are returned at any time.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

MySQL Method – using LIMIT

If you’ve used MySQL at all, you might be familiar with syntax like this:

SELECT *
FROM yourtable
ORDER BY name
LIMIT 50, 10;

This query would get rows 51 to 60, ordered by the name column.

This works in MySQL because the ORDER BY happens before the LIMIT. So, you can get the rows from 51-60 using this LIMIT clause.

However, there is no LIMIT keyword in Oracle, even through it is in the SQL standard.

If you try this in Oracle using the ROWNUM pseudocolumn, it won’t work. This is because ROWNUM is evaluated before the ORDER BY.

The ROWNUM pseudocolumn is not stored on the row or table permanently.

So, let’s say you have a query like this:

SELECT *
FROM yourtable
WHERE rownum <= 10
ORDER BY name;

This has a few problems:

It will show a random selection of 10 records, because they are not ordered at the time the WHERE clause is applied
It also doesn’t allow for a starting point or an offset (getting rows 51-60 for example)

So, how do we get the results we want?

How to Select the Top N Rows in Oracle SQL

To find the top N rows in Oracle SQL, there is one recommended way to do it.

This method was suggested by AskTom from Oracle.com.

SELECT * FROM (
  SELECT *
  FROM yourtable
  ORDER BY name
)
WHERE ROWNUM <= 10;

This query will get the first 10 records. The important point here is that it uses a subquery to do the ordering first, and then the outer query performs the rownum limiting.

The rownum is assigned after the ORDER BY because it’s in the outer query.

You could change this query to suit your needs.

How to Write an Oracle Pagination Query

The other common reason to use this type of query is for pagination.

For example, if you want to show page 2 of a search results page, you might want to show results 11 to 20. Or, from 26 to 50. Or something like that.

It’s generally better to perform this using database logic, as it’s easier to do than in the client, and also helps with database performance.

So, how do you limit the number of rows returned by an Oracle query for pagination?

There are a few ways to do this.

Method 1 – AskTom

This method is also recommended by AskTom. It’s the same method as above, but it uses both a min and a max row number.

SELECT * FROM (
  SELECT  /*+ FIRST_ROWS(n) */
  t.*, ROWNUM rnum FROM (
    SELECT *
    FROM yourtable
    ORDER BY name
  ) t
  WHERE ROWNUM <= :MAX_ROW_TO_FETCH)
WHERE rnum >= :MIN_ROW_TO_FETCH;

You can see a few things in this query:

The FIRST_ROWS(n) is called an optimiser hint, and tells Oracle you want to optimise for getting the first rows.
MAX_ROW_TO_FETCH is the last row you want to fetch (e.g. if you’re looking for rows 51 to 60, set this to 60)
MIN_ROW_TO_FETCH is the last row you want to fetch (e.g. if you’re looking for rows 51 to 60, set this to 51)

Method 2 – Analytic Query

Another way of getting a subset of results that have been ordered is by using an analytic query.

For example:

SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY name) rnum
  FROM yourtable t
)
WHERE rnum BETWEEN 51 AND 60;

This query uses the analytic function ROW_NUMBER, which returns a row number for each row ordered by the field specified (in this case, the name field). This can also work in SQL Server.

Method 3 – Fetch

In Oracle 12c, a new method for limiting rows or starting at offsets was introduced.

SELECT *
FROM yourtable
ORDER BY name
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;

This query will get you the first 10 rows, starting from row 51, as an “offset” has been applied on the first 50 rows.

Which Method To Limit the Number of Rows in Oracle is the Best?

Well, the best method for limiting rows in Oracle will consider performance, flexibility, and actually what database version you have.

If you’re using Oracle 12c, then use the FETCH syntax, as it was built especially for this purpose.

If you’re not using Oracle 12c, I would suggest using the AskTom method, as it was recommended by Tom Kyte and has been used by many Oracle users.

So, that’s how you can write a query to limit the number of rows returned in Oracle. If you have any questions on this, let me know in the comments section.