ROLLUP and RANK

The following query uses ROLLUP and RANK to get the salary ranks by 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',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    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 deptno, SUM(sal),
  2  RANK() OVER (ORDER BY SUM(sal) DESC) AS rank
  3  FROM emp
  4  GROUP BY ROLLUP(deptno)
  5  ORDER BY deptno;

    DEPTNO   SUM(SAL)       RANK
---------- ---------- ----------
        10       5850          4
        20       8275          3
        30       8450          2
                22575          1

SQL>
Home »
Oracle »
Analytical Functions » 

RANK:
  1. RANK
  2. Place NULLS FIRST in RANK
  3. Place NULLS LAST in RANK
  4. Place NULLS LAST in RANK and order by clause
  5. Get top n rows with rank
  6. Using the RANK with group by
  7. RANK() vs DENSE_RANK() Functions
  8. RANK() and PARTITION BY
  9. ROLLUP and RANK
  10. CUBE and RANK
  11. GROUPING SETS and RANK
Related: