Using FIRST_VALUE & LAST_VALUE in Oracle Analytic Functions

In this blog, we will see how to use FIRST_VALUE & LAST_VALUE in Oracle with simple examples.

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. 

The syntax for FIRST_VALUE and LAST_VALUE:

FIRST_VALUE (<<COLUMN NAME &gt;&gt;) OVER (ORDER BY <<COLUMN NAME &gt;&gt; )

LAST_VALUE (<<COLUMN NAME &gt;&gt;) OVER (ORDER BY <<COLUMN NAME &gt;&gt; )

  . 

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. 

hank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.