MAX returns the largest value in a group: the top salary of each department, the latest hire, the worst delay. Like MIN it works on any sortable type, including intervals in Oracle AI Database 26ai, and it is easily confused with GREATEST, which compares values across a row rather than down a column.
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:
max([distinct | all] expr)
MAX ignores NULLs and returns NULL for an empty group.
Latest Hire and Top Salary
The first query finds the latest hire date; the second the top salary of each department.
Example:
select count(*) as employees,
count(commission_pct) as with_commission,
count(distinct department_id) as departments,
sum(salary) as payroll,
round(avg(salary), 2) as avg_salary,
min(hire_date) as first_hire,
max(hire_date) as last_hire
from employees;
select department_id, count(*) as staff, sum(salary) as payroll, max(salary) as top_salary
from employees
group by department_id
order by department_id
fetch first 4 rows only;Output:
EMPLOYEES WITH_COMMISSION DEPARTMENTS PAYROLL AVG_SALARY FIRST_HIRE LAST_HIRE
____________ __________________ ______________ __________ _____________ ______________ ______________
55 7 10 803400 14607.27 01-MAR-2012 02-FEB-2026
DEPARTMENT_ID STAFF PAYROLL TOP_SALARY
________________ ________ __________ _____________
10 2 84000 48000
20 12 213800 27000
30 11 99100 22000
40 10 142100 34000The Worst Delay
MAX of an interval returns the largest interval: here the worst departure delay of two routes.
Example:
select r.origin || '-' || r.destination as route, count(*) as flights,
sum(f.actual_arrival - f.actual_departure) as time_in_air,
avg(f.actual_arrival - f.actual_departure) as average_flight,
max(f.actual_departure - f.scheduled_departure) as worst_delay
from flights f join routes r on r.route_id = f.route_id
where f.status = 'ARRIVED' and r.origin = 'DXB' and r.destination in ('LHR', 'SYD')
group by r.origin, r.destination;Output:
ROUTE FLIGHTS TIME_IN_AIR AVERAGE_FLIGHT WORST_DELAY __________ __________ ______________________ _________________________ ______________________ DXB-LHR 72 +20 01:52:00.000000 +00 06:41:33.333333333 +00 01:24:00.000000 DXB-SYD 30 +17 13:30:00.000000 +00 14:03:00.000000 +00 01:18:00.000000
The worst delay from Dubai to London was 1 hour and 24 minutes.
MAX Compared with GREATEST
MAX works down a column, over the rows of a group. GREATEST works across its arguments, within one row.
Example:
-- MAX works down a column, GREATEST across values of one row
select max(seats_business) as max_business_seats, max(seats_economy) as max_economy_seats,
max(greatest(seats_business, seats_economy)) as max_of_either
from aircraft_types;
select type_code, greatest(seats_business, seats_economy) as larger_cabin
from aircraft_types
order by type_code;Output:
MAX_BUSINESS_SEATS MAX_ECONOMY_SEATS MAX_OF_EITHER
_____________________ ____________________ ________________
42 312 312
TYPE_CODE LARGER_CABIN
____________ _______________
A20N 150
A21N 180
A359 283
B77W 312
B789 260MAX(GREATEST(...)) combines both: the largest cabin of each aircraft type, then the largest of those.
Things to Know
- To get the row with the maximum, use KEEP (DENSE_RANK LAST ORDER BY ...), ROW_NUMBER with QUALIFY, or FETCH FIRST 1 ROW ONLY.
- Unlike MAX, GREATEST returns NULL if any of its arguments is NULL.
- MAX(date) is a quick way to find the latest activity per group.
Related Guides
Conclusion
MAX returns the largest non-NULL value of a group for numbers, text, dates, and intervals. Use GREATEST for the largest value within a row, and KEEP, QUALIFY, or FETCH FIRST when you need the whole row with the maximum.
