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.