Using DENSE_RANK and RANK together
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
NewSAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 810, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 1610, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 1210, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 2915, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 1210, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 2810, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 2810, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 3010, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 3010, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 1510, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 1510, 20);
SQL>
SQL> select job,
2 RANK() over(order by sal),
3 DENSE_RANK() over(order by sal)
4 from emp;
JOB RANK()OVER(ORDERBYSAL) DENSE_RANK()OVER(ORDERBYSAL)
--------- ---------------------- ----------------------------
CLERK 1 1
SALESMAN 2 2
SALESMAN 2 2
SALESMAN 4 3
CLERK 4 3
SALESMAN 6 4
MANAGER 7 5
MANAGER 7 5
MANAGER 9 6
PRESIDENT 10 7
ANALYST 10 7
11 rows selected.
SQL>
Home »
Oracle »
Analytical Functions »
Oracle »
Analytical Functions »
DENSE_RANK:
- DENSE_RANK
- Place NULLS FIRST in DENSE_RANK
- Place NULLS LAST in DENSE_RANK
- Get top n rows with dense_rank
- Using the DENSE_RANK with group by
- Using DENSE_RANK and RANK together
- DENSE_RANK() and PARTITION BY
- ROLLUP and DENSE_RANK
- CUBE and DENSE_RANK
- GROUPING SETS and DENSE_RANK
Related: