FIRST and LAST Analytic Functions in Oracle SQL

FIRST
FIRST function is used to return the first value from the ordered sequence.

LAST
LAST function is used to return the last value from the ordered sequence.

Example:
Display the salary of the employees with lowest and highest in the department.
You can use Over clause to make the output more redefine as department wise.

SELECT employee_id,
department_id,
salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) OVER (PARTITION BY department_id) AS lowest,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY salary) OVER (PARTITION BY department_id) AS highest
FROM employees
ORDER BY department_id, salary;

EMPLOYEE_ID DEPARTMENT_ID     SALARY     LOWEST    HIGHEST
----------- ------------- ---------- ---------- ----------
        200            10       4400       4400       4400
        202            20       6000       6000      13000
        201            20      13000       6000      13000
        119            30       2500       2500      11000
        118            30       2600       2500      11000
        117            30       2800       2500      11000
        116            30       2900       2500      11000
        115            30       3100       2500      11000
        114            30      11000       2500      11000