# What is ROWNUM=1 ?

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

select rownum, ename
from emp
order by ename;
ROWNUM ENAME
---------- ----------
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

## 2 Replies to “What is ROWNUM=1 ?”

1. Brian Ballsun-Stanton says:

Interesting. Which version is this? Also, could you post the source of your sample data? This would make an excellent exercise for the performance and tuning class I’m helping with.

2. the source is the default scott schema that can be installed with @?/rdbms/admin/utlsampl

CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES