Oracle SQL - Analytic Functions Partitions

Introduction

A partition is a set of rows defined in the result set.

The default partition for any function is the entire result set.

You can have one partition clause per function.

The PARTITION BY clause must come before the ORDER BY clause.

When a partition is defined, the rows belonging to each partition are grouped together and the function is applied within each group.

In the following code, one RANK is for the entire company and the second RANK is within each department.

Ranking Employee Salary Within the Company and Department

drop table emp;
create table emp( 
empno      NUMBER(4)    primary key, 
ename      VARCHAR2(8)  not null   , 
init       VARCHAR2(5)  not null   , 
job        VARCHAR2(8)             , 
mgr        NUMBER(4)               , 
bdate      DATE         not null   , 
msal       NUMBER(6,2)  not null   , 
comm       NUMBER(6,2)             , 
deptno     NUMBER(2)    default 10) ;

insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);


SELECT e1.deptno, e1.ename, e1.msal,
       RANK() OVER (ORDER BY e1.msal DESC) sal_rank,
       RANK() OVER (PARTITION BY e1.deptno
                    ORDER BY e1.msal DESC) dept_sal_rank
FROM emp e1
ORDER BY e1.deptno ASC, e1.msal DESC;

Demo

SQL>
SQL> SELECT e1.deptno, e1.ename, e1.msal,
  2         RANK() OVER (ORDER BY e1.msal DESC) sal_rank,
  3         RANK() OVER (PARTITION BY e1.deptno
  4                      ORDER BY e1.msal DESC) dept_sal_rank
  5  FROM emp e1--   w w w .  j  a va2s  . c o m
  6  ORDER BY e1.deptno ASC, e1.msal DESC;

   DEPTNO | ENAME    |      MSAL |  SAL_RANK | DEPT_SAL_RANK
--------- | -------- | --------- | --------- | -------------
    1     | KING     |  05000.00 |  00002.00 |      00001.00
    1     | BLAKE    |  02850.00 |  00006.00 |      00002.00
    1     | CLARK    |  02450.00 |  00007.00 |      00003.00
    1     | MARY     |  01300.00 |  00011.00 |      00004.00
    1     | WARD     |  01250.00 |  00012.00 |      00005.00
    2     | SCOTT    |  03000.00 |  00003.00 |      00001.00
    2     | FORD     |  03000.00 |  00003.00 |      00001.00
    2     | JACK     |  02975.00 |  00005.00 |      00003.00
    2     | SMITH    |  01800.00 |  00008.00 |      00004.00
    2     | ADAMS    |  01100.00 |  00014.00 |      00005.00
    30    | JONES    |  08000.00 |  00001.00 |      00001.00

   DEPTNO | ENAME    |      MSAL |  SAL_RANK | DEPT_SAL_RANK
--------- | -------- | --------- | --------- | -------------
    30    | ALLEN    |  01600.00 |  00009.00 |      00002.00
    30    | BREAD    |  01500.00 |     1     |      00003.00
    30    | BROWN    |  01250.00 |  00012.00 |      00004.00

14 rows selected.

SQL>

Related Topics