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,
|
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; |