Using FIRST_VALUE & LAST_VALUE in Oracle Analytic Functions

FIRST_VALUE and LAST_VALUE in Oracle

These are Oracle analytical functions used to return the first value or the last value from a set of ordered rows. These functions can get the first value or the last value within a column in a table. The only argument that can be passed to these functions is the column name. To use these functions we must use the ORDER BY clause. A partition by clause can be used but not required to use these functions.  The functions are identical and the only difference is the name. 

Syntax for FIRST_VALUE and LAST_VALUE:

FIRST_VALUE (<<COLUMN NAME >>) OVER (ORDER BY <<COLUMN NAME >> )

LAST_VALUE (<<COLUMN NAME >>) OVER (ORDER BY <<COLUMN NAME >> )

  . 

The above syntax diagram is taken from the Oracle SQL Reference 19c document. 

Now let see some example on how we can use them and determine the need based on actual business requirements. Now if we need to generate a report where we need to show the employee information along with another column to show which employee is getting the least paid or high paid. 

Below is a simple example of how to write a FIRST_VALUE analytical function:

 From the above query, we see that the least paid employee is "Smith" but if we need to display the report say based on departments with the order then we can add an order by clause at the end, in our case we are trying to retrieve the rows based on salary and least paid person. So in our scenario, we should order by salary as shown below. One thing to notice that the least paid employees column values will be repeated and the same follows on LAST_VALUE too. 

Now, for instance, we need to get the highest paid person then simply we need to change the order but not at the end but at analytical clause:

CODE:

select ename, job,deptno, sal,
first_value(ename) over (order by sal desc) least_paid_employee
from scott.emp
order by sal;

LAST_VALUE just works as same as FIRS T_VALUE, so let us try to use the same SQL statement just by changing the function name from "FIRST_VALUE" to "LAST_VALUE".

The SQL statement will return the lowest paid employee name against each salary range in the employee table.  

The range is salary because we have provided the salary as the range, we can also identify by department number too. 

select ename, job,deptno, sal,
last_value(ename) over (order by sal desc) least_paid_employee
from scott.emp
order by sal

From the above query, we see that for each range of salary the lowest paid employee is repeated for the same sections. 

For example, "WARD" is repeated for 1250 salary 

The above statement shows for each salary range which employee gets the lowest salary now if we want to check for each department then we can change the statement as like below which will make more information on how/where we can utilize these analytical functions any business use case. 

select ename, job,deptno, sal,
last_value(ename) over (order by deptno desc) least_paid_employee
from scott.emp
order by deptno

Now from the above result, we can see that for department number 20 Smith is the employee who is getting a low salary. But the data is not ordered by the department. If we add the salary on the order by then we see a clear result which person is the lowest paid within the same department. 

select ename, job,deptno, sal,
last_value(ename) over (order by deptno desc) least_paid_employee
from scott.emp
order by deptno,sal

So now we understand how to use the "FIRST_VALUE" & "LAST_VALUE"  function in Oracle. 

Thank you for reading my article, please feel free to leave me some feedback or to suggest any future topics. I'll be looking forward to hearing from you – Swadhin Ray (Sloba)

For more information about me, please check out my Experts Exchange Profile page.