Oracle PL/SQL FIRST and LAST Function

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. The FIRST and LAST functions eliminate the need for self joins or views and enable better performance.

Syntax aggregate_function KEEP (DENSE_RANK FIRST ORDER BY Order_by_clause) [OVER (query_partitioning_clause)] aggregate_function KEEP (DENSE_RANK LAST ORDER BY Order_by_clause) [OVER (query_partitioning_clause)]

Example :

SELECT student_no,

       deptno,

       total_marks,

       MIN(total_marks) KEEP (DENSE_RANK FIRST ORDER BY total_marks) OVER (PARTITION BY deptno) "Lowest_Mark",

       MAX(total_marks) KEEP (DENSE_RANK LAST ORDER BY total_marks) OVER (PARTITION BY deptno) "Highest_Mark"

FROM   students_table

ORDER BY deptno, total_marks;



STUDENT_NO   DEPTNO  TOTAL_MARKS     LOWEST_MARK    HIGHEST_MARK

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

      1001         100       1400       1350       1600

      1008         100       1350       1350       1600

      1009         100       1600       1350       1600

      1005         200        850        850       3000

      1006         200       1100        850       3500

      1002         200       2975        850       3500

      1003         200       3500        850       3500

      1004         200       3000        850       3500

      1007         200        950        850       3500



      

Difference Between Oracle RANK() and DENSE_RANK()

RANK

Let's assume we want to assign a rank to students in a college department wise based on total_marks. Below is the example with the results
when a RANK() function is used.

SELECT studentno,

       deptno,

       total_marks,

       RANK() OVER (PARTITION BY deptno ORDER BY total_marks) "rank"

FROM   students_table;



     STUDENT_NO     DEPTNO        total_marks       rank
    ----------      --------       ----------       -------
 
      1001         100               1300          1

      1005         100               1300          1

      1006         100               1400          3

      1009         200               1100          1

      1002         200               1150          2

      1004         200               1150          2

      1003         200               1200          4

  From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is not consecutive. Now lets see how using a DENSE_RANK() will change these results.

DENSE_RANK

SELECT studentno,

       deptno,

       total_marks,

       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY total_marks) "rank"

FROM   student_table;



     STUDENT_NO     DEPTNO        total_marks       rank

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

      1001         100               1300          1

      1005         100               1300          1

      1006         100               1400          2

      1009         200               1100          1

      1002         200               1150          2

      1004         200               1150          2

      1003         200               1200          3

From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is consecutive.

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.