How to use Oracle PL/SQL DENSE_RANK Fucntion

What is Oracle DENSE_RANK Function ?

In Oracle PL/SQL, DENSE_RANK function is a built in analytic function which is used to rank (consecutively) a record within a group of row.

The only difference between the DENSE_RANK() and RANK() functions is that RANK() will assign non-consecutive ranks to the values within a group in the case of a tie.

Example :

SELECT DEPT, STUDENT_ID,TOTAL_MARKS,DENSE_RANK() OVER 

(PARTITION BY DEPT ORDER BY TOTAL_MARKS) RANK

FROM STUDENTS_TABLE

/


      DEPT      STUDENT_ID    TOTAL_MARKS       RANK
   
----------      ----------   ------------      ---------

        100      100001        100               1

        100      100009        200               2
                                              
        101      100005        101               1

        101      100004        101               1
                                                  
        101      100002        102               2 
                             
        101      100006        102               2
 
        101      100007        103               3

7 rows selected.

How to use Oracle PL/SQL Rank function

What is Oracle Rank Function ?

Oracle Rank() function

In Oracle PL/SQL, RANK function is a built in analytic function which is used to rank a record within a group of row.

 The rank function can cause non-consecutive rankings if the tested values are the same. If you need to have the consecutive rankings you need to use dense_rank() function.

Example Syntax:

Used as an Aggregate function:

RANK (expression) WITHIN GROUP (ORDER_BY expression [ASC | DESC] NULLS [FIRST | LAST]  )

Used as an Analytic function:

RANK () OVER (PARTITION BY expression ORDER BY expression)

Example Usage:

The below SQL query uses demonstrates analytic behavior of RANK function. It ranks the salary of the employees working in the same department.

SELECT DEPT, STUDENT_ID, RANK() OVER 

(PARTITION BY DEPT ORDER BY TOTAL_MARKS) RANK

FROM STUDENTS_TABLE

/



      DEPT      STUDENT_ID   RANK

----------      ----------   ---------

        100      100001        1

        100      100009        2

        101      100005        1

        101      100004        2

        101      100002        3

        102      100006        1

        102      100007        2

       

7 rows selected.