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.
Labels: , , ,