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