8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Articles » Misc » Here

# RANK, DENSE_RANK, FIRST and LAST Analytic Functions

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

Related articles.

## RANK

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 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

SQL>```

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.

## DENSE_RANK

The `DENSE_RANK` function acts like the `RANK` function except that it assigns consecutive ranks.

```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

SQL>```

## FIRST and LAST

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 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

SQL>```