The FIRST and LAST Aggregate Functions

One of my favorite aggregate functions in Oracle is FIRST (and of course its counterpart, LAST; ok, so two of my favorite aggregate functions).

The very common aggregate functions MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.

The FIRST and LAST functions extend this ability, and allow you to sort a group of rows by one column, but return the value of another column from the first or last row in the sorted group.

For example, what is the FIRST_NAME of the employee who was most recently hired (i.e., with the latest HIRE_DATE)?

Before we see the (somewhat cumbersome) syntax, we should realize that this question may be a tricky one – it is not guaranteed that just one employee has the latest HIRE_DATE. There may be several records with the same value.

In the simple MIN and MAX functions this is not a problem, because we return the value of the column that was used for the sorting, so it’s the same value for all the records with the lowest value (for MIN) or highest value (for MAX).

To make sure FIRST and LAST are deterministic, we must define which row to consider, out of the sub-group of rows with the lowest/highest value. We do this by using a simple aggregate function.

So, to make the question from the example complete: what is the FIRST_NAME of the employee who was most recently hired (i.e., with the latest HIRE_DATE), and if there are several employees hired on the same day get the lexicographically first one?

select min(first_name) keep (dense_rank last order by hire_date)
from emp;

Read this in the following order:

1. order by hire_date
2. last (look at the sub-group of rows with the last value)
3. first_name (in this sub-group look at the values of first_name)
4. min (return the minimum value of first_name from this sub-group)

Another example: what is the FIRST_NAME of the employee with the oldest HIRE_DATE, and if there are several employees hired on the same day get the lexicographically first one?

select min(first_name) keep (dense_rank first order by hire_date)
from emp;

And finally, what is the FIRST_NAME of the employee with the oldest HIRE_DATE, and if there are several employees hired on the same day get the lexicographically last one?

select max(first_name) keep (dense_rank first order by hire_date)
from emp;