LAST_VALUE Function without PARTITION BY Clause in Oracle SQL

The LAST_VALUE Function in Oracle SQL / PLSQL is an analytical function and is used to return the last value in an ordered set of values.

If the last value in the ordered set is NULL then LAST_VALUE function returns NULL unless we specify IGNORE NULLS.
If we specify IGNORE NULLS then LAST_VALUE function returns the first NON NULL value in the ordered list, or NULL if the list contains all the NULL values.
LAST_VALUE function can be used with and without PARTITION BY clause.

Syntax for using the LAST_VALUE function in Oracle SQL / PLSQL is:
SELECT column(s)
,LAST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table_name;

Example 1:
Using LAST_VALUE Function Without IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Suppose we wish to view the employee_id, employee_name, department and the last value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission) OVER (ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Department	Minimum_Commission
108	Emp H	Support
107	Emp G	Sales
106	Emp F	Sales
105	Emp E	Sales
104	Emp D	Support
103	Emp C	IT
102	Emp B	IT
101	Emp A	Sales

Here we can see that we have fetched NULL values in the ‘Minimum_Commission’ column because we have sorted the list in descending order of ‘employee_id’, and for ‘employee_id = 101’ the ‘commission’ is NULL, hence NULL becomes the last value that is encountered and is therefore fetched.

Example 2:

Using LAST_VALUE Function With IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Suppose we wish to view the employee_id, employee_name, department and the last value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission IGNORE NULLS) OVER (ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Department	Minimum_Commission
108	Emp H	Support	20
107	Emp G	Sales	20
106	Emp F	Sales	20
105	Emp E	Sales	20
104	Emp D	Support	20
103	Emp C	IT	20
102	Emp B	IT	20
101	Emp A	Sales	20

Here we can see that we have fetched 20 as the commission value even though the last value encountered was NULL by using IGNORE NULLS clause in LAST_VALUE function.

20 has been returned because the LAST_VALUE function will return the FIRST NON NULL value if a NULL value is encountered at last while using IGNORE NULLS clause.