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.