FIRST_VALUE and LAST_VALUE Analytic Functions in Oracle SQL

FIRST_VALUE
FIRST function is used to return the first value from the ordered sequence.
If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS value.
{RESPECT | IGNORE} NULLS determines whether null values returned or return next first value.

LAST_VALUE
LAST function is used to return the last value from the ordered sequence.
If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS Value.
{RESPECT | IGNORE} NULLS determines whether null values returned or return next last value.

Windowing_clause:
Defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Example of First Value:
Return the name of the employee with the lowest salary.

SELECT department_id, last_name, salary,
FIRST_VALUE(last_name)
OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY employee_id)
ORDER BY last_name;

  
DEPARTMENT_ID LAST_NAME     SALARY LOWEST_SAL 
------------- ------------- ------ -----------
           90 De Haan        17000 Kochhar
           90 King           24000 Kochhar
           90 Kochhar        17000 Kochhar

Example of Last Value:
Return the hire date of the employee earning the highest salary.

SELECT last_name, salary, hire_date,
LAST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) AS lv
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY hire_date);

LAST_NAME    SALARY HIRE_DATE LV
------------ ------ --------- ---------
De Haan       17000 13-JAN-01 17-JUN-03
Kochhar       17000 21-SEP-05 17-JUN-03
King          24000 17-JUN-03 17-JUN-03