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 LAST 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 with PARTITION BY and 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 department wise 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 (PARTITION BY department 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 |
103 | Emp C | IT | 20 |
102 | Emp B | IT | 20 |
107 | Emp G | Sales | |
106 | Emp F | Sales | |
105 | Emp E | Sales | |
101 | Emp A | Sales | |
108 | Emp H | Support | 5 |
104 | Emp D | Support | 5 |
Here we can see that we have fetched LAST values for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 102’ has 20 commission and hence is fetched against ‘IT’ department.
For ‘Sales’ department the ‘employee_id = 101’ have NULL as commission, hence NULL is fetched as LAST value against ‘Sales’ department.
For ‘Support’ department the ‘employee_id = 104’ has 5 as commission, hence 5 is fetched as LAST value against ‘Support’ department.
Example 2:
Using LAST_VALUE Function with PARTITION BY and 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 department wise 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 (PARTITION BY department 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 |
103 | Emp C | IT | 20 |
102 | Emp B | IT | 20 |
107 | Emp G | Sales | 10 |
106 | Emp F | Sales | 10 |
105 | Emp E | Sales | 10 |
101 | Emp A | Sales | 10 |
108 | Emp H | Support | 5 |
104 | Emp D | Support | 5 |
Here we can see that we have fetched LAST values for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 102’ has 20 commission and hence is fetched against ‘IT’ department.
Also observe that for ‘employee_id = 101’ in Sales department the LAST value encountered is NULL, but the next NON NULL value is 10 for ‘employee_id =105’ and hence 10 is fetched for ‘Sales’ department.
Similarly for ‘Support’ department, ‘employee_id = 104’ has commission of and hence 5 is fetched for other employees of ‘Support’ department.