How do you return the top-N results of a query in Oracle? Why doesn’t the obvious method work? | Oracle 10g Interview Questions

 Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don’t get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;