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;

If you look at the table without a row number restriction, you'll find some missing entries. Why? 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;

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;