Oracle Top-N query: are results guaranteed to be ordered?

Question

Most sources, including Oracle's own Top-N Queries article, give syntax similar to the following for performing a Top-N query:

SELECT val
FROM (
    SELECT val
    FROM rownum_order_test
    ORDER BY val DESC
)
WHERE ROWNUM <= 5;

This is guaranteed to give the top 5 values; however, is it guaranteed to give them in the correct order? This question, citing Wikipedia, indicates otherwise:

Although some database systems allow the specification of an ORDER BY clause in subselects or view definitions, the presence there has no effect.

Does this mean that the aforementioned code does not guarantee any ordering, and requires an additional ORDER BY, or is it an exceptional case?

SELECT val
FROM (
    SELECT val
    FROM rownum_order_test
    ORDER BY val DESC
)
WHERE ROWNUM <= 5
ORDER BY val DESC;   -- Is this line necessary?

Edit: Assume that the outer query will be executed directly (not nested in another query). I'm posing this question because it feels like a glaring omission that none of the sources (including Oracle's) bothered to mention the need for the ORDER BY, despite that ordering would be intuitively expected from such top-n queries.

Answer 1

The order by clearly has an effect here, because if it did not then the query simply would not work to provide the top-n values.

There may well be cases where the optimiser can detect that there is no effect provided by an ORDER BY, and in such cases it might transform the query to remove the ORDER BY, but this is not one of those cases.

If your outer query does not re-order the rows, either explicitly or implicitly (through a join, perhaps), then I would be confident that in this case the order will be preserved.

Answer 2

In older versions of ORACLE (8.0) you don't have the possibility to use ORDER BY clause in subquery. So, only for those of us who yet use some ancient versions, there is another way to deal with: The magic of UNION operator. UNION will sort the records by columns in the query:

Example:

SELECT * FROM
(SELECT EMP_NO, EMP_NAME FROM EMP_TABLE
UNION
SELECT 99999999999,'' FROM DUAL)
WHERE ROWNUM<=5;

where 99999999999 is bigger then all values in EMP_NO

Or, if you want to select TOP 5 salary employees with the highest 5 salaries:

SELECT EMP_NO, EMP_NAME, 99999999999999-TMP_EMP_SAL
FROM
(SELECT 99999999999999-EMP_SAL TMP_EMP_SAL, EMP_NO, EMP_NAME 
FROM EMP_TABLE
UNION
SELECT 99999999999999,0,'' FROM DUAL)
WHERE ROWNUM<=5;

Regards, Virgil Ionescu