Analytical functions

There are several analytical functions that have been around for a while. I thought writing about them is a good idea since they are widely ignored

In this discussion EMP and DEPT tables are used, the following scripts build them in case you do not have them

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));



INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);


CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

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');

 

Rank Function

 SQL>SELECT ename, sal, comm,
RANK() OVER (ORDER BY sal DESC) AS sal_rank FROM emp
order by sal desc;

ENAME             SAL       COMM   SAL_RANK

---------- ---------- ---------- ----------

KING             5000                     1

SCOTT            3000                     2

FORD             3000                     2

JONES            2975                     4

BLAKE            2850                     5

CLARK            2450                     6

ALLEN            1600        300          7

TURNER           1500          0          8

MILLER           1300                     9

WARD             1250        500         10

MARTIN           1250       1400         10

ADAMS            1100                    12

JAMES             950                    13

SMITH             800                    14

King is Rank number 1, SCOTT , FORD are tie on Second.

Dense Rank

SQL> SELECT ename, sal, comm,

  2  DENSE_RANK() OVER (ORDER BY sal DESC) AS sal_rank FROM emp

  3  order by sal desc;

ENAME             SAL       COMM   SAL_RANK

---------- ---------- ---------- ----------

KING             5000                     1

SCOTT            3000                     2

FORD             3000                     2

JONES            2975                     3

BLAKE            2850                     4

CLARK            2450                     5

ALLEN            1600        300          6

TURNER           1500          0          7

MILLER           1300                     8

WARD             1250        500          9

MARTIN           1250       1400          9

ADAMS            1100                    10

JAMES             950                    11

SMITH             800                    12

What is the difference between RANK() and DENSE_RANK()  functions

Note the Rank sequence in the first examples 1,2,2,4   and the second 1,2,2,3