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.