Limit number of rows in a sorted query
When you work with ORDER BY and a row number restriction, you'll sometimes get an unexpected result. Let's have a look at the wrong approach first:
SELECT ename FROM emp WHERE ROWNUM <= 5 ORDER BY ename
This will produce a result like this:
If you look at the table without a row number restriction, you'll find a second person called ADAMS. Why's that person not there? The answer is simple, it's a matter of order execution. The WHERE part is executed before the ORDER BY which means that only the first 5 rows are sorted and not the whole table.
Recipe #1 - Sorting a table with a row number restriction the proper way
The following query uses a sub-query to produce the right result:
SELECT ename FROM ( SELECT ename FROM emp ORDER BY ename ) WHERE ROWNUM <= 5
And the actual output:
Recipe #2 - Limiting result with Oracle 12c
Since Oracle 12C R1 there's a row limiting clause which makes things a bit more like LIMIT, but it's still quite different.
SELECT * FROM emp ORDER BY ename FETCH FIRST 5 ROWS ONLY