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,

1 |
SELECT * FROM emp LIMIT 5; |

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.

1 |
SELECT ROWNUM, emp.* FROM emp; |

So we can limit the number of returning rows:

1 |
SELECT * FROM emp WHERE ROWNUM <= 5; |

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:

1 |
SELECT * FROM (SELECT * FROM emp ORDER BY first_name) WHERE ROWNUM <= 5; |

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

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

Your post deserved this after so many years ðŸ™‚

Thanks!

You are welcome! ðŸ™‚

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

Thiro, I’m glad to hear that it helps.

Thanks

You are welcome! ðŸ™‚

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

Awesome WorkÂ Gokhan,

Exactly the thing i was searching from last whole week.

I really appreciate this work

Feeling Fulfilled,

Zeeshan

Thank you for a quick easy solution!