Thinking about using rownum while using it in TOP N Query with ORDER BY Clause: BEAWARE Series-II

First :

SELECT ename, sal FROM
(SELECT ename, sal FROM emp ORDER BY sal DESC)
WHERE rownum <=3;


ENAME SAL
---------- ---------
KING 5000
SCOTT 3000
FORD 3000

3 rows selected

 Second:

SELECT ename, sal FROM emp
WHERE rownum <=3
ORDER BY sal DESC




ENAME SAL
---------- ----------------------
ALLEN 1600
WARD 1250
SMITH 800

The first will return desired result however, second does not give us the result we want
The reason is described below:

Because Oracle assigns the ROWNUM values to the rows before it does the sort.
In this example, Oracle will retrieve three rows from the table, any three rows, and sort only these three rows. We really need Oracle to sort all the rows and then return the first three. The inline view will ensure that this will happen


Post Reference: Vikram Aristocratic Elfin Share

The Help21X-Men: First Class (+Digital Copy) [Blu-ray]A Time to Heal (Quilts of Lancaster County)