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

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;

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.

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated. View all posts by sqlandplsql