Difference in RANK, DENSE_RANK, FIRST and LAST Analytic Functions

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

First create a table STUDENT and insert some sample records to perform the task.

CREATE TABLE STUDENT (
STUDENT_ID NUMBER(4),
DEPT VARCHAR2(20),
SCHOLARSHIP NUMBER(5));

INSERT INTO STUDENT VALUES(2001,'CS',800);
INSERT INTO STUDENT VALUES(2002,'CS',950);
INSERT INTO STUDENT VALUES(2003,'CS',1100);
INSERT INTO STUDENT VALUES(2004,'EC',1250);
INSERT INTO STUDENT VALUES(2005,'EC',1250);
INSERT INTO STUDENT VALUES(2006,'EC',1300);
INSERT INTO STUDENT VALUES(2007,'EC',1500);
INSERT INTO STUDENT VALUES(2008,'EC',1600);
INSERT INTO STUDENT VALUES(2009,'IT',2450);
INSERT INTO STUDENT VALUES(2010,'IT',2850);
INSERT INTO STUDENT VALUES(2011,'IT',2950);
INSERT INTO STUDENT VALUES(2012,'IT',3000);
INSERT INTO STUDENT VALUES(2013,'IT',3000);
INSERT INTO STUDENT VALUES(2014,'IT',5000);

COMMIT;

RANK Function

Suppose we want to assign a sequential order/ rank to students within a dept based on their scholarship, we may use the RANK function like below example.
You will see here, when two people have the same scholarship they will be assigned the same rank. When multiple rows share the same rank the next rank in the sequence will be skipped.

SELECT STUDENT_ID,
       DEPT,
       SCHOLARSHIP,
       RANK() OVER (PARTITION BY DEPT ORDER BY SCHOLARSHIP) as RANK
FROM   STUDENT;

STUDENT_ID DEPT                 SCHOLARSHIP       RANK
---------- -------------------- ----------- ----------
      2001 CS                           800          1
      2002 CS                           950          2
      2003 CS                          1100          3
      2004 EC                          1250          1
      2005 EC                          1250          1
      2006 EC                          1300          3
      2007 EC                          1500          4
      2008 EC                          1600          5
      2009 IT                          2450          1
      2010 IT                          2850          2
      2011 IT                          2950          3
      2012 IT                          3000          4
      2013 IT                          3000          4
      2014 IT                          5000          6

14 rows selected.

SQL>

DENSE_RANK Function

The DENSE_RANK function works like the RANK function except that it does not skip the next rank in the sequence.

SELECT STUDENT_ID,
       DEPT,
       SCHOLARSHIP,
       DENSE_RANK() OVER (PARTITION BY DEPT ORDER BY SCHOLARSHIP) as RANK
FROM   STUDENT;

STUDENT_ID DEPT                 SCHOLARSHIP       RANK
---------- -------------------- ----------- ----------
      2001 CS                           800          1
      2002 CS                           950          2
      2003 CS                          1100          3
      2004 EC                          1250          1
      2005 EC                          1250          1
      2006 EC                          1300          2
      2007 EC                          1500          3
      2008 EC                          1600          4
      2009 IT                          2450          1
      2010 IT                          2850          2
      2011 IT                          2950          3
      2012 IT                          3000          4
      2013 IT                          3000          4
      2014 IT                          5000          5

14 rows selected.

SQL>

FIRST and LAST Function

The FIRST and LAST functions is used to return the first or last value from an ordered sequence. Suppose we want to show the scholarship of each student along with the lowest and highest scholarship within their dept we may use the following script.

SELECT STUDENT_ID,
       DEPT,
       SCHOLARSHIP,
       MIN(SCHOLARSHIP) KEEP (DENSE_RANK FIRST ORDER BY SCHOLARSHIP) OVER (PARTITION BY DEPT) as LOWEST,
       MAX(SCHOLARSHIP) KEEP (DENSE_RANK LAST ORDER BY SCHOLARSHIP) OVER (PARTITION BY DEPT) as HIGHEST
FROM   STUDENT
ORDER BY DEPT,SCHOLARSHIP;

STUDENT_ID DEPT                 SCHOLARSHIP     LOWEST    HIGHEST
---------- -------------------- ----------- ---------- ----------
      2001 CS                           800        800       1100
      2002 CS                           950        800       1100
      2003 CS                          1100        800       1100
      2004 EC                          1250       1250       1600
      2005 EC                          1250       1250       1600
      2006 EC                          1300       1250       1600
      2007 EC                          1500       1250       1600
      2008 EC                          1600       1250       1600
      2009 IT                          2450       2450       5000
      2010 IT                          2850       2450       5000
      2011 IT                          2950       2450       5000
      2012 IT                          3000       2450       5000
      2013 IT                          3000       2450       5000
      2014 IT                          5000       2450       5000

14 rows selected.

SQL>

Don’t forget to drop the table after experimenting with the article.

SQL> DROP TABLE STUDENT;
Table dropped.
SQL>

Hope you liked the article.