FIRST and LAST Functions

Operate on a set of rows that rank as the FIRST or LAST with respect to a given sorting specification, in many cases only one row will rank as FIRST or LAST.

Note that the value retrieved from the first or last row need not be from the column on which the group was sorted.

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)]


Order by clause:
   ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST }]
         [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST }]]...

query_partitioning_clause:
   PARTITION BY value/expr [,value/expr...]

Dense_Rank:
DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank ("olympic rank").

aggregate_functions:
Any one of MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV.
The function will only operate on those rows that rank either FIRST or LAST (often a single row).

OVER
Specifying the OVER (PARTITION... clause will apply the FIRST and LAST functions as analytic functions (rather than as simple aggregate functions).

Example (aggregate function):

SELECT department_id,
       MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id;

DEPARTMENT_ID      Worst       Best
------------- ---------- ----------
           10       4400       4400
           20       6000      13000
           30       2500      11000
           40       6500       6500
           50       2100       8200
...

This displays the best and worst salary for employees in each department.

Example (analytic function):
For each employee within the department

SELECT last_name, department_id, salary,
       MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
          OVER (PARTITION BY department_id) "Worst",
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
          OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary;

LAST_NAME           DEPARTMENT_ID     SALARY      Worst       Best
------------------- ------------- ---------- ---------- ----------
Whalen                         10       4400       4400       4400
Goyal                          20       6000       6000      13000
Hartstein                      20      13000       6000      13000
...

This displays the employees salary along with the best and worst salaries for all employees in the same dept.


"This is not the end. It is not even the beginning of the end. It is perhaps the end of the beginning" - Winston Churchill

Back to Analytic features

List of all functions