# FIRST_VALUE and LAST_VALUE analytical functions

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.