How to Limit the Number of Rows Returned in Oracle

I worked with MySQL for a long time, so I know how useful to limit the number of returning rows of a query. For example,

Unfortunately, Oracle does not have an operator like LIMIT. On the other hand, we can emulate it by using ROWNUM pseudo column. ROWNUM will tell the row number.

So we can limit the number of returning rows:

Unfortunately, if we add ORDER BY, GROUP BY operators to the query, this method will not work, because rownum will show the row number before “sorting”. So we use rownum in a subquery:

Please share this post Share on Facebook0Share on Google+0Share on LinkedIn0Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database architect who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

14 Comments

  1. Clvr

    It helped me a lot especially for the ORDER BY combination.

    Your post deserved this after so many years 🙂

    Thanks!

  2. Thiro

    Hi Gokhan,

    Thanks a lot for this. This worked and this is exactly what I was looking for.

    It took more than one hour to get this article.

    Cheers,

    Thiro

  3. David Corcoran

    I expected this to work, and it does:

    select * from foo where rownum between 10 and 20;

    to match the offset/limit directive/operators in mysql

  4. zeeshan

    Awesome Work Gokhan,

    Exactly the thing i was searching from last whole week.

    I really appreciate this work

    Feeling Fulfilled,

    Zeeshan

  5. Akshay

    Hi Atil,
    Is is possible to limit the number of rows in such a way such that it is irrespective of the database we use . I want to write a query in such a way where my service which implements the query need not know which database it is using but i still want to leverage the concept of limiting my resultset with maybe 100 values rather than millions .

    Would be really helpful if you can suggest a way for a generic query .

    Thanks,
    Akshay

    • Gokhan Atil

      Akshay, unfortunately each database have its own keywords to limit rows, so it’s not possible to write a generic query.

  6. Tony Eastwood

    Really useful – saved me the  huge effort of upgrading to a later version of Oracle just for this one feature .

Leave Comment

Your email address will not be published. Required fields are marked *