Both FIRST_VALUE and LAST_VALUE are analytical functions in Oracle.
FIRST_VALUE
It is used to find the first value from a ordered set of values.
Syntax :-
1) FIRST_VALUE(column) over(partition by column order by column )
2) FIRST_VALUE(column) ignore nulls over(partition by column order by column )
— Above will ignore null column’s
3) FIRST_VALUE(column) over(partition by column order by column ) ROWS UNBOUNDED PRECEDING
Example:-
Consider below EMP table structure
| EMPNNO | EMPNAME | SALARY | DEPTNO |
| 10 | Bill | 12000 | 5 |
| 11 | Solomon | 10000 | 5 |
| 12 | Susan | 10000 | 5 |
| 13 | Wendy | 9000 | 1 |
| 14 | Benjamin | 7500 | 1 |
| 15 | Tom | 7600 | 1 |
| 16 | Henry | 8500 | 2 |
| 17 | Robert | 9500 | 2 |
| 18 | Paul | 7700 | 2 |
| 19 | Dora | 8500 | 3 |
| 20 | Samuel | 6900 | 3 |
| 21 | Mary | 7500 | 3 |
| 22 | Daniel | 6500 | 4 |
| 23 | Ricardo | 7800 | 4 |
| 24 | Mark | 7200 | 4 |
Consider we need to construct a query to find one ( only one ) name of employee who earns lowest salary in his department.
SQL> select empno,salary,deptno,
first_value(empno) over(partition by deptno order by salary asc ROWS UNBOUNDED PRECEDING ) as first_value
from emp order by empno asc;
| EMPNO | EMPNAME | DEPTNO | FIRST_VALUE |
| 10 | 12000 | 5 | 11 |
| 11 | 10000 | 5 | 11 |
| 12 | 10000 | 5 | 11 |
| 13 | 9000 | 1 | 14 |
| 14 | 7500 | 1 | 14 |
| 15 | 7600 | 1 | 14 |
| 16 | 8500 | 2 | 18 |
| 17 | 9500 | 2 | 18 |
| 18 | 7700 | 2 | 18 |
| 19 | 8500 | 3 | 20 |
| 20 | 6900 | 3 | 20 |
| 21 | 7500 | 3 | 20 |
| 22 | 6500 | 4 | 22 |
| 23 | 7800 | 4 | 22 |
| 24 | 7200 | 4 | 22 |
LAST_VALUE
It is used to find the last value from a ordered set of values.
Syntax :-
1) LAST_VALUE(column) over(partition by column order by column )
2) LAST_VALUE(column) ignore nulls over(partition by column order by column )
— Above will ignore null column’s
3) LAST_VALUE(column) over(partition by column order by column ) ROWS UNBOUNDED PRECEDING
Same way what we find FIRST_VALUE, LAST_VALUE returns last columns value within the window function
Word of caution :-
Both FIRST_VALUE and LAST_VALUE will have sort operation and might have performance impact. So use above analytical functions with care for relative large queries.
