RANK, DENSE_RANK, FIRST and LAST Analytic Functions

The following examples use the EMP table from the SCOTT sample schema, which is created as follows.

CONN sys/password AS SYSDBA

-- Create SCOTT schema if you don't currently have it.
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

Let's assume we want to assign a sequential order, or rank, to people within a department based on salary, we might use the RANK function like:

SELECT deptno,
       ename,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp
WHERE  deptno = 30;

    DEPTNO ENAME             SAL       rank
---------- ---------- ---------- ----------
        30 JAMES             950          1
        30 WARD             1250          2
        30 MARTIN           1250          2
        30 TURNER           1500          4
        30 ALLEN            1600          5
        30 BLAKE            2850          6

What we see here is where two people have the same salary they are assigned the same rank. When multiple rows share the same rank the next rank in the sequence is not consecutive.

The DENSE_RANK function works acts like the RANK function except that it assigns consecutive ranks:

SELECT deptno,
       ename,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp
WHERE  deptno = 30;

    DEPTNO ENAME             SAL       rank
---------- ---------- ---------- ----------
        30 JAMES             950          1
        30 WARD             1250          2
        30 MARTIN           1250          2
        30 TURNER           1500          3
        30 ALLEN            1600          4
        30 BLAKE            2850          5

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. Say we want to display the salary of each employee, along with the lowest and highest within their department we may use something like:

SELECT deptno,
       ename,
       sal,
       MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
       MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM   emp
ORDER BY deptno, sal;

    DEPTNO ENAME             SAL     Lowest    Highest
---------- ---------- ---------- ---------- ----------
        10 MILLER           1300       1300       5000
        10 CLARK            2450       1300       5000
        10 KING             5000       1300       5000
        20 SMITH             800        800       3000
        20 ADAMS            1100        800       3000
        20 JONES            2975        800       3000
        20 SCOTT            3000        800       3000
        20 FORD             3000        800       3000
        30 JAMES             950        950       2850
        30 WARD             1250        950       2850
        30 MARTIN           1250        950       2850

    DEPTNO ENAME             SAL     Lowest    Highest
---------- ---------- ---------- ---------- ----------
        30 TURNER           1500        950       2850
        30 ALLEN            1600        950       2850
        30 BLAKE            2850        950       2850

For more information see:

Hope this helps. Regards Tim...

Back to the Top.