Comparing each row with the first row of its group is a common need: each salary against the first hire's, each day's price against the opening price, each step against the starting value. FIRST_VALUE returns the value of an expression from the first row of the window, on every row.
Code for This Guide
The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
first_value(expr) [{respect | ignore} nulls]
over ([partition by ...] order by ... [frame])FIRST_VALUE returns expr from the first row of the window. With the default frame, which starts at the beginning of the partition, that is the first row of the partition. IGNORE NULLS returns the first non-NULL value.
First, Last, and Second Hire
This query uses a named window covering the whole department to return the first, last, and second hire on every row.
Example:
select department_id, last_name, hire_date,
first_value(last_name) over w as first_hired,
last_value(last_name) over w as last_hired,
nth_value(last_name, 2) over w as second_hired
from employees
where department_id in (50, 60)
window w as (partition by department_id order by hire_date
rows between unbounded preceding and unbounded following)
order by department_id, hire_date;Output:
DEPARTMENT_ID LAST_NAME HIRE_DATE FIRST_HIRED LAST_HIRED SECOND_HIRED
________________ ____________ ______________ ______________ _____________ _______________
50 Mehta 01-SEP-2015 Mehta Schmidt Reddy
50 Reddy 06-JUN-2016 Mehta Schmidt Reddy
50 Williams 18-FEB-2019 Mehta Schmidt Reddy
50 Schmidt 04-SEP-2023 Mehta Schmidt Reddy
60 Okafor 01-FEB-2016 Okafor Mensah Costa
60 Costa 29-JAN-2018 Okafor Mensah Costa
60 Mensah 09-MAY-2022 Okafor Mensah Costa
7 rows selected.Compare with the First Row
Example:
-- each salary compared with that of the department's first hire
select department_id, last_name, hire_date, salary,
first_value(salary) over (partition by department_id order by hire_date) as first_hire_salary,
salary - first_value(salary) over (partition by department_id order by hire_date) as difference
from employees
where department_id = 60
order by hire_date;Output:
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY FIRST_HIRE_SALARY DIFFERENCE
________________ ____________ ______________ _________ ____________________ _____________
60 Okafor 01-FEB-2016 29000 29000 0
60 Costa 29-JAN-2018 11200 29000 -17800
60 Mensah 09-MAY-2022 7400 29000 -21600Each employee's salary is compared with the first hire's, Okafor's 29,000. For FIRST_VALUE the default frame is fine, because the window always starts at the first row; it is LAST_VALUE that needs an explicit frame.
Things to Know
- With an ORDER BY that has ties at the start, the first row among the tied ones is not defined; add a tie-breaker.
- FIRST_VALUE with a frame such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW returns the first value of a moving window.
- Within an aggregate query, MIN(x) KEEP (DENSE_RANK FIRST ORDER BY ...) returns the same value once per group.
Related Guides
Conclusion
FIRST_VALUE returns the value from the first row of each row's window, by default the first row of the partition. Use it to compare every row with the starting value of its group, and IGNORE NULLS to skip missing values.
