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.
- 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 FIST_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:
- 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...