RANK, DENSE_RANK, FIRST and LAST Analytic Functions
This article gives an overview of the RANK
, DENSE_RANK
, FIRST
and LAST
analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
- Ranking using RANK, DENSE_RANK, ROW_NUMBER
- Analytic Functions : All 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
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE; CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
RANK
The basic description for the RANK
analytic function is shown below. The analytic clause is described in more detail here.
RANK() OVER ([ query_partition_clause ] order_by_clause)
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 this.
SELECT empno, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank FROM emp; EMPNO DEPTNO SAL MYRANK ---------- ---------- ---------- ---------- 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. This is like olympic medaling in that if two people share the gold, there is no silver medal etc.
The fact we can rank the rows in the department means we are able to do a Top-N query on a per-department basis. The example below assigns the rank in the inline view, then uses that rank to restrict the rows to the bottom 2 (worst paid) employees in each department.
SELECT * FROM (SELECT empno, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank FROM emp) WHERE myrank <= 2; EMPNO DEPTNO SAL MYRANK ---------- ---------- ---------- ---------- 7934 10 1300 1 7782 10 2450 2 7369 20 800 1 7876 20 1100 2 7900 30 950 1 7521 30 1250 2 7654 30 1250 2 SQL>
DENSE_RANK
The basic description for the DENSE_RANK
analytic function is shown below. The analytic clause is described in more detail here.
DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)
The DENSE_RANK
function acts like the RANK
function except that it assigns consecutive ranks, so this is not like olympic medaling.
SELECT empno, deptno, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank FROM emp; EMPNO DEPTNO SAL MYRANK ---------- ---------- ---------- ---------- 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>
As with the RANK
analytic function, we can do a Top-N query on a per-department basis. The example below assigns the dense rank in the inline view, then uses that rank to restrict the rows to the top 2 (best paid) employees in each department.
SELECT * FROM (SELECT empno, deptno, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS myrank FROM emp) WHERE myrank <= 2; EMPNO DEPTNO SAL MYRANK ---------- ---------- ---------- ---------- 7839 10 5000 1 7782 10 2450 2 7788 20 3000 1 7902 20 3000 1 7566 20 2975 2 7698 30 2850 1 7499 30 1600 2 SQL>
FIRST and LAST
You should probably avoid FIRST
and LAST
in favour of the FIRST_VALUE and LAST_VALUE Analytic Functions, which follow the normal analytic function syntax. I always use these in preference to FIRST
and LAST
now.
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) AS lowest, MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS 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>
The MIN
and MAX
functions are almost irrelevant here as it's FIRST
, LAST
and KEEP
that are picking the row whose value will be used. We can demonstrate this by using MIN
for both the high and low value.
SELECT empno, deptno, sal, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS lowest, MIN(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS 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>
We get the same result.
We could also achieve the same result using FIRST_VALUE
and LAST_VALUE
, or MIN
and MAX
as basic analytic functions. In practice I don't use FIRST
and LAST
very often.
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
- Ranking using RANK, DENSE_RANK, ROW_NUMBER
- Analytic Functions : All Articles
- Analytic Functions
- FIRST_VALUE and LAST_VALUE Analytic Functions
- Top-N Queries
- RANK
- DENSE_RANK
- FIRST
- LAST
Hope this helps. Regards Tim...