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