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
  • 1

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.


  1. Clvr

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

    Your post deserved this after so many years 🙂


  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.



  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,


  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 .


    • 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 .

  7. WITH base AS ( select * — get the table from sometable order by name — in the desired order ), twenty AS ( select * — get the first 30 rows from base where rownum = 30 order by name — in the desired order ) select * — then get rows 21 .. 30 from twenty where rownum 20 order by name — in the desired order

Leave a Reply to Clvr Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.