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;
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 ww .j av a 2 s . co 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>