Problem

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.

Solution

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