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:
ENAME |
---|
ALLEN |
JONES |
MARTIN |
SMITH |
WARD |
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:
ENAME |
---|
ADAMS |
ALLEN |
BLAKE |
CLARK |
FORD |
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
Comments