Questions such as "what was the salary of the first person hired in each department" or "which route out of each airport is the longest" need a value from the row that ranks first or last by another column. Without help, that means a subquery or an analytic function. Oracle's KEEP (DENSE_RANK FIRST | LAST ORDER BY ...) clause answers it inside an ordinary aggregate.
Code for This Guide
The main examples are in the examples/aggregate-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:
aggregate_function(expr) keep (dense_rank {first | last} order by expr [asc | desc] [, ...])KEEP restricts the aggregate to the rows that rank first, or last, by the ORDER BY. The aggregate in front (MIN, MAX, SUM, COUNT, AVG) then decides what to return from those rows, which matters when several rows tie for first place.
First and Last Hire per Department
Example:
select department_id,
min(salary) keep (dense_rank first order by hire_date) as first_hire_salary,
max(last_name) keep (dense_rank last order by hire_date) as newest_employee,
max(hire_date) as newest_hire_date
from employees
where department_id in (20, 30, 40)
group by department_id;Output:
DEPARTMENT_ID FIRST_HIRE_SALARY NEWEST_EMPLOYEE NEWEST_HIRE_DATE
________________ ____________________ __________________ ___________________
20 27000 Rossi 01-OCT-2024
30 22000 O'Brien 25-AUG-2025
40 34000 Tanaka 06-JAN-2025MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) is the salary of the earliest hire, and MAX(last_name) KEEP (DENSE_RANK LAST ORDER BY hire_date) the name of the latest. If two people had been hired on the same first day, MIN would pick the lower of their salaries.
Longest and Shortest Route per Airport
Example:
-- the longest route from each of three airports, and its distance
select origin,
max(destination) keep (dense_rank last order by distance_km) as longest_to,
max(distance_km) as km,
min(destination) keep (dense_rank first order by distance_km) as shortest_to
from routes
where origin in ('DXB', 'SIN', 'LHR')
group by origin
order by origin;Output:
ORIGIN LONGEST_TO KM SHORTEST_TO _________ _____________ ________ ______________ DXB AKL 14200 BOM LHR JFK 5540 DXB SIN SYD 6294 NRT
For each airport, LAST by distance gives the destination of the longest route, and FIRST the shortest, in one grouped query.
KEEP Compared with the Alternatives
| Approach | Notes |
|---|---|
| KEEP (DENSE_RANK FIRST ...) | One grouped query; several values from different rankings at once |
| ROW_NUMBER with QUALIFY | Returns whole rows; one ranking per query block |
| Correlated subquery | Works everywhere, but repeats the lookup for each value |
Things to Know
- Only DENSE_RANK is allowed inside KEEP.
- KEEP also works as an analytic function: max(x) keep (dense_rank last order by d) over (partition by g).
- The ORDER BY can have several columns to break ties before the aggregate does.
Related Guides
Conclusion
KEEP (DENSE_RANK FIRST | LAST ORDER BY ...) restricts an aggregate to the rows that rank first or last by another column, so one grouped query can return values from the earliest, latest, longest, or shortest row of each group.
