Difference in RANK, DENSE_RANK, FIRST and LAST Analytic Functions

Rank Dense_Rank

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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • justin

    Hi Anil,

    Thanks for the article, it is very useful. I have a question regarding the first and last. Does the max(SCHOLARSHIP)
    and min(SCHOLARSHIP) has any difference, I try using both max as below and it return the same result as using a max and min?

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

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)