RANK, DENSE_RANK, FIRST and LAST Analytic Functions

This article gives and overview of the RANK, DENSE_RANK, FIRST and LAST analytic functions.

• RANK
• DENSE_RANK
• FIRST and LAST

Related articles.

• Analytic Functions
• FIRST_VALUE and LAST_VALUE Analytic Functions
• LAG and LEAD Analytic Functions
• LISTAGG Analystic Function in 11g Release 2
• Top-N Queries

SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6 

DENSE_RANK

SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5 

FIRST and LAST

SELECT empno,
       deptno,
       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;

     EMPNO     DEPTNO        SAL     Lowest    Highest
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       5000
      7782         10       2450       1300       5000
      7839         10       5000       1300       5000
      7369         20        800        800       3000
      7876         20       1100        800       3000
      7566         20       2975        800       3000
      7788         20       3000        800       3000
      7902         20       3000        800       3000
      7900         30        950        950       2850
      7654         30       1250        950       2850
      7521         30       1250        950       2850
      7844         30       1500        950       2850
      7499         30       1600        950       2850
      7698         30       2850        950       2850

 

FIRST_VALUE

The FIRST_VALUE analytic function is similar to the FIRST analytic function, allowing you to return the first result from an ordered set.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS 
         OVER (PARTITION BY deptno ORDER BY sal) AS lowest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT
---------- ---------- ---------- --------------
      7934         10       1300           1300
      7782         10       2450           1300
      7839         10       5000           1300
      7369         20        800            800
      7876         20       1100            800
      7566         20       2975            800
      7788         20       3000            800
      7902         20       3000            800
      7900         30        950            950
      7654         30       1250            950
      7521         30       1250            950
      7844         30       1500            950
      7499         30       1600            950
      7698         30       2850            950 

The "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when determining results.
The windowing clause can be used to alter the window of operation. The following example uses "ROWS 1 PRECEDING" to give a result similar, but not quite the same, to a LAG of 1 row.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal ROWS 1 PRECEDING) AS preceding_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL PRECEDING_IN_DEPT
---------- ---------- ---------- -----------------
      7934         10       1300              1300
      7782         10       2450              1300
      7839         10       5000              2450
      7369         20        800               800
      7876         20       1100               800
      7566         20       2975              1100
      7788         20       3000              2975
      7902         20       3000              3000
      7900         30        950               950
      7654         30       1250               950
      7521         30       1250              1250
      7844         30       1500              1250
      7499         30       1600              1500
      7698         30       2850              1600 

LAST_VALUE

The LAST_VALUE analytic function is similar to the LAST analytic function, allowing you to return the last result from an ordered set. Using the default windowing clause the result can be a little unexpected.

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            1300
      7782         10       2450            2450
      7839         10       5000            5000
      7369         20        800             800
      7876         20       1100            1100
      7566         20       2975            2975
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950             950
      7654         30       1250            1250
      7521         30       1250            1250
      7844         30       1500            1500
      7499         30       1600            1600
      7698         30       2850            2850

This is because the default windowing clause is

 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", 

which in this example means the current row will always be the last value. 

Altering the windowing clause to 

"RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" 

gives us the result we probably expected. 

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN
           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            5000
      7782         10       2450            5000
      7839         10       5000            5000
      7369         20        800            3000
      7876         20       1100            3000
      7566         20       2975            3000
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950            2850
      7654         30       1250            2850
      7521         30       1250            2850
      7844         30       1500            2850
      7499         30       1600            2850
      7698         30       2850            2850