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)

Thinking about using rownum in where clause: BEAWARE Series-I

First :

SELECT rnum, table_name FROM
(SELECT rownum rnum, table_name FROM user_tables)
WHERE rnum > 2;

Second:

SELECT table_name FROM user_tables
WHERE rownum > 2;

The first will return result whereas the second will zero rows.


The reason is described below:
However, this query will always return zero rows, regardless of the number of rows in the table.

To explain this behavior, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.
The bottom line is that conditions such as the following will work as expected.
WHERE rownum = 1;
WHERE rownum <= 10;
WHERE rownum <=3;
While queries with these conditions will always return zero rows.

WHERE rownum = 2;
WHERE rownum > 10;



Post Reference: Vikram Aristocratic Elfin Share