Mega Code Archive
Rank and dense_rank over partition
<![CDATA[
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL> select deptno, ename, sal,
2 dense_rank() over ( partition by deptno order by sal desc ) dr,
3 rank() over ( partition by deptno order by sal desc ) r
4 from emp
5 order by deptno, sal desc
6 /
DEPTNO ENAME SAL DR R
------ ---------- -------- -------- --------
10 KING 5000.00 1.00 1.00
CLARK 2450.00 2.00 2.00
MILLER 1300.00 3.00 3.00
20 SCOTT 3000.00 1.00 1.00
FORD 3000.00 1.00 1.00
JONES 2975.00 2.00 3.00
ADAMS 1100.00 3.00 4.00
SMITH 800.00 4.00 5.00
30 BLAKE 2850.00 1.00 1.00
DEPTNO ENAME SAL DR R
------ ---------- -------- -------- --------
30 ALLEN 1600.00 2.00 2.00
TURNER 1500.00 3.00 3.00
MARTIN 1250.00 4.00 4.00
WARD 1250.00 4.00 4.00
JAMES 950.00 5.00 6.00
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
]]>