What is ROWNUM=1 ?

Is rownum=1 the first row returned? well, it depends :


select rownum, ename
from emp
order by ename;
ROWNUM ENAME
---------- ----------
11 ADAMS
2 ALLEN
6 BLAKE
7 CLARK
13 FORD
12 JAMES
4 JONES
9 KING
5 MARTIN
14 MILLER
8 SCOTT
1 SMITH
10 TURNER
3 WARD

The ROWNUM is evaluated before the order by

select rownum
from emp
group by rownum
having rownum between 3 and 6;
ROWNUM
----------
6
5
3
4

the rownum is selected before the GROUP BY and before the HAVING.

select
rownum,
sal,
median(sal) over ()
from emp;
ROWNUM SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
1 800 1550
12 950 1550
11 1100 1550
3 1250 1550
5 1250 1550
14 1300 1550
10 1500 1550
2 1600 1550
7 2450 1550
6 2850 1550
4 2975 1550
8 3000 1550
13 3000 1550
9 5000 1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function