Analytical function executing order
A sql like:
select *
from employee
where employee_first_name = 'Jason';
is executed by scanning the table employee and apply the where clause. The where clause is also called the "row filter".
For:
select *
from employee
where employee_first_name = 'Jason'
group by ...
having ...;
Having is called the "after filter"(after row filter) and is executed after group by clause.
For:
select *
from employee
where employee_first_name = 'Jason'
group by ...
having ...
order by ...;
The order by clause is done last.
The analytical function is performed before the order by clause.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select empno, ename, job, sal, deptno from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
1 SMITH CLERK 800 20
2 ALLEN SALESMAN 1600 30
3 WARD SALESMAN 1250 30
4 JONES MANAGER 2975 20
5 MARTIN SALESMAN 1250 30
6 BLAKE MANAGER 2850 30
7 CLARK MANAGER 2850 10
8 SCOTT ANALYST 3000 20
9 KING PRESIDENT 3000 10
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK 1500 20
11 rows selected.
SQL>
SQL> select empno, ename, job, sal, deptno from emp
2 where sal > 1000;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
2 ALLEN SALESMAN 1600 30
3 WARD SALESMAN 1250 30
4 JONES MANAGER 2975 20
5 MARTIN SALESMAN 1250 30
6 BLAKE MANAGER 2850 30
7 CLARK MANAGER 2850 10
8 SCOTT ANALYST 3000 20
9 KING PRESIDENT 3000 10
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK 1500 20
10 rows selected.
SQL>
SQL> select empno, ename, job, sal, deptno from emp
2 where sal > 1000
3 order by sal;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
3 WARD SALESMAN 1250 30
5 MARTIN SALESMAN 1250 30
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK 1500 20
2 ALLEN SALESMAN 1600 30
7 CLARK MANAGER 2850 10
6 BLAKE MANAGER 2850 30
4 JONES MANAGER 2975 20
9 KING PRESIDENT 3000 10
8 SCOTT ANALYST 3000 20
10 rows selected.
SQL>
SQL> select empno, ename, job, sal, deptno, rank() over (order by sal) from emp
2 where sal > 1000
3 order by sal;
EMPNO ENAME JOB SAL DEPTNO RANK()OVER(ORDERBYSAL)
---------- ---------- --------- ---------- ---------- ----------------------
3 WARD SALESMAN 1250 30 1
5 MARTIN SALESMAN 1250 30 1
10 TURNER SALESMAN 1500 30 3
11 ADAMS CLERK 1500 20 3
2 ALLEN SALESMAN 1600 30 5
7 CLARK MANAGER 2850 10 6
6 BLAKE MANAGER 2850 30 6
4 JONES MANAGER 2975 20 8
9 KING PRESIDENT 3000 10 9
8 SCOTT ANALYST 3000 20 9
10 rows selected.
SQL>
SQL> select empno, ename, job, sal, deptno, rank() over (order by sal) from emp
2 where sal > 1000
3 order by sal;
EMPNO ENAME JOB SAL DEPTNO RANK()OVER(ORDERBYSAL)
---------- ---------- --------- ---------- ---------- ----------------------
3 WARD SALESMAN 1250 30 1
5 MARTIN SALESMAN 1250 30 1
10 TURNER SALESMAN 1500 30 3
11 ADAMS CLERK 1500 20 3
2 ALLEN SALESMAN 1600 30 5
7 CLARK MANAGER 2850 10 6
6 BLAKE MANAGER 2850 30 6
4 JONES MANAGER 2975 20 8
9 KING PRESIDENT 3000 10 9
8 SCOTT ANALYST 3000 20 9
10 rows selected.
SQL>