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:

9 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 🙂


  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.



  3. Frank Suarez says:


  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,



Leave a Reply

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