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:

12 Responses to “How to Limit the Number of Rows Returned in Oracle”

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

    Your post deserved this after so many years 🙂

    Thanks!

  2. 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. Frank Suarez says:

    Thanks

  4. David Corcoran says:

    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

  5. Awesome Work Gokhan,

    Exactly the thing i was searching from last whole week.

    I really appreciate this work

    Feeling Fulfilled,

    Zeeshan

  6. Sue Cox says:

    Thank you for a quick easy solution!

  7. Akshay says:

    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 says:

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

  8. Thanks ! Really helpful

Leave a Reply

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