FIRST and LAST Analytic Functions in Oracle SQL

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.