Analytics - More ranking options

View All Scripts Login to Run Script

Script Name Analytics - More ranking options
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script is for additional ranking options across an entire table. Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Wednesday December 16, 2015

Statement 1
This clear the EMP table in your own schema. If you're running this on your own database, make sure EMP is not a name you already have in use !
```
drop table emp
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

We're creating everything from scratch here, so not only can you run this in LiveSQL, but you can download or clipboard the script and run it on your own database.

create table emp (
EMPNO    NUMBER(4)    ,
ENAME             VARCHAR2(10) ,
JOB               VARCHAR2(9)  ,
MGR               NUMBER(4)    ,
HIREDATE          DATE         ,
SAL               NUMBER(7,2)  ,
COMM              NUMBER(7,2)  ,
DEPTNO            NUMBER(2)    
)

Table created.

Statement 3

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17/DEC/80','DD/MON/RR'),800,null,20)

1 row(s) inserted.

Statement 4

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20/FEB/81','DD/MON/RR'),1600,300,30)

1 row(s) inserted.

Statement 5

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22/FEB/81','DD/MON/RR'),1250,500,30)

1 row(s) inserted.

Statement 6

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02/APR/81','DD/MON/RR'),2975,null,20)

1 row(s) inserted.

Statement 7

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28/SEP/81','DD/MON/RR'),1250,1400,30)

1 row(s) inserted.

Statement 8

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01/MAY/81','DD/MON/RR'),2850,null,30)

1 row(s) inserted.

Statement 9

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09/JUN/81','DD/MON/RR'),2450,null,10)

1 row(s) inserted.

Statement 10

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('09/DEC/82','DD/MON/RR'),3000,null,20)

1 row(s) inserted.

Statement 11

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17/NOV/81','DD/MON/RR'),5000,null,10)

1 row(s) inserted.

Statement 12

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08/SEP/81','DD/MON/RR'),1500,null,30)

1 row(s) inserted.

Statement 13

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('12/JAN/83','DD/MON/RR'),1100,null,20)

1 row(s) inserted.

Statement 14

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03/DEC/81','DD/MON/RR'),950,null,30)

1 row(s) inserted.

Statement 15

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03/DEC/81','DD/MON/RR'),3000,null,20)

1 row(s) inserted.

Statement 16

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23/JAN/82','DD/MON/RR'),1300,null,10)

1 row(s) inserted.

Statement 17
```
commit
```
```
Statement processed.
```

Statement 18

Here is our base data ordered by salary

select empno, ename, job, hiredate, sal 
from emp 
order by sal

 
EMPNO ENAME JOB HIREDATE SAL
7369 SMITH CLERK 17-DEC-80 800
7900 JAMES CLERK 03-DEC-81 950
7876 ADAMS CLERK 12-JAN-83 1100
7521 WARD SALESMAN 22-FEB-81 1250
7654 MARTIN SALESMAN 28-SEP-81 1250
7934 MILLER CLERK 23-JAN-82 1300
7844 TURNER SALESMAN 08-SEP-81 1500
7499 ALLEN SALESMAN 20-FEB-81 1600
7782 CLARK MANAGER 09-JUN-81 2450
7698 BLAKE MANAGER 01-MAY-81 2850
7566 JONES MANAGER 02-APR-81 2975
7788 SCOTT ANALYST 09-DEC-82 3000
7902 FORD ANALYST 03-DEC-81 3000
7839 KING PRESIDENT 17-NOV-81 5000

14 rows selected.

EMPNO	ENAME	JOB	HIREDATE	SAL
7369	SMITH	CLERK	17-DEC-80	800
7900	JAMES	CLERK	03-DEC-81	950
7876	ADAMS	CLERK	12-JAN-83	1100
7521	WARD	SALESMAN	22-FEB-81	1250
7654	MARTIN	SALESMAN	28-SEP-81	1250
7934	MILLER	CLERK	23-JAN-82	1300
7844	TURNER	SALESMAN	08-SEP-81	1500
7499	ALLEN	SALESMAN	20-FEB-81	1600
7782	CLARK	MANAGER	09-JUN-81	2450
7698	BLAKE	MANAGER	01-MAY-81	2850
7566	JONES	MANAGER	02-APR-81	2975
7788	SCOTT	ANALYST	09-DEC-82	3000
7902	FORD	ANALYST	03-DEC-81	3000
7839	KING	PRESIDENT	17-NOV-81	5000

Statement 19

And a recap of the RANK function from video #1

select empno, ename, job, hiredate, sal,
       rank() OVER (order by hiredate) as hire_seq
from emp 
order by sal

 
EMPNO ENAME JOB HIREDATE SAL HIRE_SEQ
7369 SMITH CLERK 17-DEC-80 800 1
7900 JAMES CLERK 03-DEC-81 950 10
7876 ADAMS CLERK 12-JAN-83 1100 14
7521 WARD SALESMAN 22-FEB-81 1250 3
7654 MARTIN SALESMAN 28-SEP-81 1250 8
7934 MILLER CLERK 23-JAN-82 1300 12
7844 TURNER SALESMAN 08-SEP-81 1500 7
7499 ALLEN SALESMAN 20-FEB-81 1600 2
7782 CLARK MANAGER 09-JUN-81 2450 6
7698 BLAKE MANAGER 01-MAY-81 2850 5
7566 JONES MANAGER 02-APR-81 2975 4
7902 FORD ANALYST 03-DEC-81 3000 10
7788 SCOTT ANALYST 09-DEC-82 3000 13
7839 KING PRESIDENT 17-NOV-81 5000 9

14 rows selected.

EMPNO	ENAME	JOB	HIREDATE	SAL	HIRE_SEQ
7369	SMITH	CLERK	17-DEC-80	800	1
7900	JAMES	CLERK	03-DEC-81	950	10
7876	ADAMS	CLERK	12-JAN-83	1100	14
7521	WARD	SALESMAN	22-FEB-81	1250	3
7654	MARTIN	SALESMAN	28-SEP-81	1250	8
7934	MILLER	CLERK	23-JAN-82	1300	12
7844	TURNER	SALESMAN	08-SEP-81	1500	7
7499	ALLEN	SALESMAN	20-FEB-81	1600	2
7782	CLARK	MANAGER	09-JUN-81	2450	6
7698	BLAKE	MANAGER	01-MAY-81	2850	5
7566	JONES	MANAGER	02-APR-81	2975	4
7902	FORD	ANALYST	03-DEC-81	3000	10
7788	SCOTT	ANALYST	09-DEC-82	3000	13
7839	KING	PRESIDENT	17-NOV-81	5000	9

Statement 20

select empno, ename, job, sal
from emp
order by sal

 
EMPNO ENAME JOB SAL
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000

14 rows selected.

EMPNO	ENAME	JOB	SAL
7369	SMITH	CLERK	800
7900	JAMES	CLERK	950
7876	ADAMS	CLERK	1100
7521	WARD	SALESMAN	1250
7654	MARTIN	SALESMAN	1250
7934	MILLER	CLERK	1300
7844	TURNER	SALESMAN	1500
7499	ALLEN	SALESMAN	1600
7782	CLARK	MANAGER	2450
7698	BLAKE	MANAGER	2850
7566	JONES	MANAGER	2975
7788	SCOTT	ANALYST	3000
7902	FORD	ANALYST	3000
7839	KING	PRESIDENT	5000

Statement 21

Now we can compare the RANK and DENSE_RANK functions when it comes to tied values in our sorting sequence

select empno, ename, job, sal,
       rank() OVER (order by sal) as sal_rank,
       dense_rank() OVER (order by sal) as sal_dense_rank
from emp
order by sal

 
EMPNO ENAME JOB SAL SAL_RANK SAL_DENSE_RANK
7369 SMITH CLERK 800 1 1
7900 JAMES CLERK 950 2 2
7876 ADAMS CLERK 1100 3 3
7521 WARD SALESMAN 1250 4 4
7654 MARTIN SALESMAN 1250 4 4
7934 MILLER CLERK 1300 6 5
7844 TURNER SALESMAN 1500 7 6
7499 ALLEN SALESMAN 1600 8 7
7782 CLARK MANAGER 2450 9 8
7698 BLAKE MANAGER 2850 10 9
7566 JONES MANAGER 2975 11 10
7788 SCOTT ANALYST 3000 12 11
7902 FORD ANALYST 3000 12 11
7839 KING PRESIDENT 5000 14 12

14 rows selected.

EMPNO	ENAME	JOB	SAL	SAL_RANK	SAL_DENSE_RANK
7369	SMITH	CLERK	800	1	1
7900	JAMES	CLERK	950	2	2
7876	ADAMS	CLERK	1100	3	3
7521	WARD	SALESMAN	1250	4	4
7654	MARTIN	SALESMAN	1250	4	4
7934	MILLER	CLERK	1300	6	5
7844	TURNER	SALESMAN	1500	7	6
7499	ALLEN	SALESMAN	1600	8	7
7782	CLARK	MANAGER	2450	9	8
7698	BLAKE	MANAGER	2850	10	9
7566	JONES	MANAGER	2975	11	10
7788	SCOTT	ANALYST	3000	12	11
7902	FORD	ANALYST	3000	12	11
7839	KING	PRESIDENT	5000	14	12

Statement 22

Here is the ROW_NUMBER function which always ascends by 1 each time, even if there are ties. For tied values, the sequencing might be indeterminate.

select empno, ename, job, sal,
       row_number() OVER (order by sal) as sal_row_number
from emp
order by sal

 
EMPNO ENAME JOB SAL SAL_ROW_NUMBER
7369 SMITH CLERK 800 1
7900 JAMES CLERK 950 2
7876 ADAMS CLERK 1100 3
7521 WARD SALESMAN 1250 4
7654 MARTIN SALESMAN 1250 5
7934 MILLER CLERK 1300 6
7844 TURNER SALESMAN 1500 7
7499 ALLEN SALESMAN 1600 8
7782 CLARK MANAGER 2450 9
7698 BLAKE MANAGER 2850 10
7566 JONES MANAGER 2975 11
7788 SCOTT ANALYST 3000 12
7902 FORD ANALYST 3000 13
7839 KING PRESIDENT 5000 14

14 rows selected.

EMPNO	ENAME	JOB	SAL	SAL_ROW_NUMBER
7369	SMITH	CLERK	800	1
7900	JAMES	CLERK	950	2
7876	ADAMS	CLERK	1100	3
7521	WARD	SALESMAN	1250	4
7654	MARTIN	SALESMAN	1250	5
7934	MILLER	CLERK	1300	6
7844	TURNER	SALESMAN	1500	7
7499	ALLEN	SALESMAN	1600	8
7782	CLARK	MANAGER	2450	9
7698	BLAKE	MANAGER	2850	10
7566	JONES	MANAGER	2975	11
7788	SCOTT	ANALYST	3000	12
7902	FORD	ANALYST	3000	13
7839	KING	PRESIDENT	5000	14

Statement 23

To ensure ROW_NUMBER is consistent, we must provide sorting information sufficient that we would not encounter a sorting "tie". In this case, EMPNO is unique and is hence sufficient for the job

select empno, ename, job, sal,
       row_number() OVER (order by sal, empno) as sal_row_number
from emp
order by sal

 
EMPNO ENAME JOB SAL SAL_ROW_NUMBER
7369 SMITH CLERK 800 1
7900 JAMES CLERK 950 2
7876 ADAMS CLERK 1100 3
7521 WARD SALESMAN 1250 4
7654 MARTIN SALESMAN 1250 5
7934 MILLER CLERK 1300 6
7844 TURNER SALESMAN 1500 7
7499 ALLEN SALESMAN 1600 8
7782 CLARK MANAGER 2450 9
7698 BLAKE MANAGER 2850 10
7566 JONES MANAGER 2975 11
7788 SCOTT ANALYST 3000 12
7902 FORD ANALYST 3000 13
7839 KING PRESIDENT 5000 14

14 rows selected.

EMPNO	ENAME	JOB	SAL	SAL_ROW_NUMBER
7369	SMITH	CLERK	800	1
7900	JAMES	CLERK	950	2
7876	ADAMS	CLERK	1100	3
7521	WARD	SALESMAN	1250	4
7654	MARTIN	SALESMAN	1250	5
7934	MILLER	CLERK	1300	6
7844	TURNER	SALESMAN	1500	7
7499	ALLEN	SALESMAN	1600	8
7782	CLARK	MANAGER	2450	9
7698	BLAKE	MANAGER	2850	10
7566	JONES	MANAGER	2975	11
7788	SCOTT	ANALYST	3000	12
7902	FORD	ANALYST	3000	13
7839	KING	PRESIDENT	5000	14

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database