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

Home » Articles » Misc » Here

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.

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.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.