PARTITION BY function

PARTITION BY function is doing the partition and separating the data. In analytical clause the PARTITION BY must precede the ORDER BY clause.

Suppose we want to partition data in employee table on department:


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',    NULL,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', NULL,    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, sal, deptno, rank() over(partition by deptno ORDER by sal)
  2  from emp
  3  order by deptno;

     EMPNO ENAME             SAL     DEPTNO RANK()OVER(PARTITIONBYDEPTNOORDERBYSAL)
---------- ---------- ---------- -------------------------------------------------
         7 CLARK            2850         10                                      1
         9 KING             3000         10                                      2
        11 ADAMS            1500         20                                      1
         4 JONES            2975         20                                      2
         8 SCOTT            3000         20                                      3
         1 SMITH                         20                                      4
         3 WARD             1250         30                                      1
         5 MARTIN           1250         30                                      1
        10 TURNER           1500         30                                      3
         6 BLAKE            2850         30                                      4
         2 ALLEN                         30                                      5


11 rows selected.

SQL>

Home »
Oracle »
Analytical Functions »

PARTITION_BY: