MAX() in SQL

Introduction

MAX() is an aggregate function that returns the largest non-NULL value from an expression. Depending on the data, that can mean highest salary, most expensive product, largest order, latest timestamp, longest duration, slowest response, highest score, or text that sorts last under a database collation.

SELECT MAX(salary) AS maximum_salary
FROM employees;

If salaries are 50,000, 60,000, 45,000, and 80,000, the result is 80,000. Reliable use requires clarity about NULL, zero, negative values, dates, text comparison, complete-row retrieval, ties, grouping, windows, joins, and indexes.

Basic Syntax

MAX(expression)

The expression is commonly one column but may be a calculated duration, signed amount, or CASE expression. The database evaluates qualifying non-NULL values and returns the greatest according to the type's comparison rules.

A Basic Example

employee_idsalary
10150000
10265000
10345000
10480000
SELECT MAX(salary) AS highest_salary
FROM employees;

The result is 80,000. A descriptive alias gives the aggregate clear business meaning in an application or report.

How MAX() Works

MAX compares qualifying expression values and retains the greatest. It does not necessarily sort every row for client output; the optimizer chooses a plan based on indexes, predicates, groups, partitions, and database capabilities.

MAX() Ignores NULL

For salaries 50,000, NULL, 80,000, and 60,000, MAX returns 80,000. NULL is missing or inapplicable information, not a numeric value larger or smaller than known salaries.

All NULL Values and Empty Results

SELECT MAX(bonus) AS maximum_bonus
FROM employees
WHERE department = 'Unknown';

If no non-NULL bonus exists, MAX returns NULL. Replacing that result with zero changes the meaning from no observed maximum to a known maximum of zero.

Zero and Negative Values

Zero is included. For -20, -10, 0, and NULL, MAX returns 0. For -100, -50, and -20, MAX returns -20 because it is numerically greatest. MAX does not find the largest absolute magnitude.

SELECT MAX(ABS(balance)) AS largest_magnitude
FROM accounts;

This separate expression finds the greatest absolute value, not the highest signed balance.

Filtering Before MAX()

SELECT MAX(salary) AS highest_active_it_salary
FROM employees
WHERE department = 'IT'
  AND status = 'Active';

WHERE defines the eligible population before MAX runs. Similar filters find the largest completed order, highest active-product price, slowest successful request, or latest unresolved defect.

MAX() with GROUP BY

SELECT department,
       MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

GROUP BY creates one set per department. If IT salaries are 60,000 and 90,000, its maximum is 90,000. If HR salaries are 50,000 and 70,000, its maximum is 70,000.

Common Grouped Maximums

SELECT category_id,
       MAX(price) AS maximum_price
FROM products
WHERE active = TRUE
GROUP BY category_id;

The same structure finds largest order by customer, highest rating by product, slowest response by endpoint, latest order by customer, or last login by user. It returns maximum values, not complete associated rows.

Filtering Groups with HAVING

SELECT department,
       MAX(salary) AS maximum_salary
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING MAX(salary) > 100000;

WHERE removes inactive employees before grouping. HAVING keeps departments containing at least one active non-NULL salary above 100,000.

MAX() with Numeric Values

Numeric uses include salary, price, amount, score, quantity, temperature, latency, duration, balance, and rating. Values must use compatible units and semantics. A mixed seconds-and-milliseconds column cannot produce a meaningful maximum without normalization.

MAX() with Dates and Timestamps

SELECT MAX(order_date) AS latest_order_date
FROM orders;

For temporal values, MAX returns the latest date or timestamp. It supports latest joining date, most recent order, last login, newest defect, latest status change, and last event in a period.

Latest Value Per Entity

SELECT customer_id,
       MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id;

This returns one date per customer represented in orders. To include customers without orders, start from customers and left join orders; their latest order date will be NULL.

Date-Filtered Maximum

SELECT MAX(order_date) AS latest_order_2026
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

The half-open range defines the year while keeping the date column directly searchable. No matching order produces NULL.

Longest Duration

The latest end time is not necessarily the longest duration. Maximize the duration expression itself.

SELECT MAX(TIMESTAMPDIFF(
         SECOND,
         start_time,
         end_time
       )) AS longest_duration_seconds
FROM jobs
WHERE end_time IS NOT NULL;

Exclude incomplete jobs or define how ongoing jobs should be measured. Validate end-before-start anomalies so a data error does not distort duration analysis.

Slowest Observations

SELECT endpoint,
       MAX(response_time_ms) AS slowest_response_ms
FROM api_logs
WHERE request_time >= NOW() - INTERVAL 1 HOUR
GROUP BY endpoint;

MAX exposes the worst observed latency but not its frequency. Pair it with count, average, P95, P99, status, payload class, and distribution.

MAX() with Text

SELECT MAX(employee_name) AS last_name_by_collation
FROM employees;

Many databases allow MAX over comparable strings. The result is the value considered greatest under the applicable collation rather than a universal dictionary order.

Collation Controls Text Maximums

Case sensitivity, accent sensitivity, locale, character set, normalization, and punctuation order can change text comparison. Test the intended collation when string MAX has business meaning, especially during database migration.

Semantic Versions Are Not Plain Text

Text MAX is unsafe for semantic versions. Under lexical comparison, 2.9 can sort after 2.10, even though semantic version 2.10 is newer. Parse version components or use a version-aware representation and comparison rule.

MAX() Returns a Value, Not a Row

For Laptop 900, Mouse 20, and Monitor 1,200, MAX(price) returns 1,200. It does not automatically return Monitor, its identifier, category, or stock status.

Selecting unrelated columns beside MAX without valid grouping can fail under standard SQL or return nondeterministic data under permissive settings.

Retrieving Every Row Tied for Maximum

SELECT *
FROM products
WHERE price = (
  SELECT MAX(price)
  FROM products
  WHERE active = TRUE
)
AND active = TRUE;

The subquery finds the maximum active price, and the outer query returns every active product tied at that value. Repeating eligibility conditions prevents mismatched populations.

Ties Are Valid Results

If two products cost 1,200, both are maximum-priced. Returning both preserves the data. A requirement for exactly one row must specify a deterministic tie-breaker rather than silently discarding one.

ORDER BY and LIMIT

SELECT *
FROM products
WHERE active = TRUE
ORDER BY price DESC, product_id ASC
LIMIT 1;

This intentionally returns one row: highest price, then lowest identifier among ties. LIMIT syntax varies by database. Omitting the second ordering column makes tied selection unstable.

Choosing the Right Maximum Pattern

RequirementPattern
Maximum value onlyMAX(column)
All rows tied for maximumFilter against a MAX subquery
Exactly one maximum rowORDER BY DESC with a tie-breaker
Maximum value per groupGROUP BY with MAX
All tied maximum rows per groupGrouped MAX joined to detail

Maximum Value Per Group

SELECT category_id,
       MAX(price) AS maximum_price
FROM products
GROUP BY category_id;

This produces one value per category. It cannot safely include arbitrary product columns because several products can share the maximum.

Complete Maximum Rows Per Group

SELECT p.*
FROM products p
JOIN (
  SELECT category_id,
         MAX(price) AS maximum_price
  FROM products
  WHERE active = TRUE
  GROUP BY category_id
) m
  ON m.category_id = p.category_id
 AND m.maximum_price = p.price
WHERE p.active = TRUE;

The derived table establishes one maximum price per category. The join returns every active product tied at each maximum.

Exactly One Maximum Row Per Group

SELECT *
FROM (
  SELECT p.*,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER BY price DESC, product_id
         ) AS rn
  FROM products p
  WHERE active = TRUE
) ranked
WHERE rn = 1;

ROW_NUMBER selects exactly one row under an explicit policy. Use RANK or DENSE_RANK when all tied maximum values should share first place.

Latest Complete Row Per Customer

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders o
) ranked
WHERE rn = 1;

This is a standard latest-row pattern. A grouped MAX alone returns the latest date but not the other columns from that order.

MAX() as a Window Function

SELECT employee_id,
       department,
       salary,
       MAX(salary) OVER (
         PARTITION BY department
       ) AS department_maximum
FROM employees;

The window form preserves every employee and adds the departmental maximum. GROUP BY would collapse the output to one row per department.

Comparing Rows to Group Maximum

SELECT employee_id,
       department,
       salary,
       MAX(salary) OVER (
         PARTITION BY department
       ) - salary AS amount_below_maximum
FROM employees;

This quantifies distance from the top salary in each group. NULL salaries produce NULL differences and do not define the maximum.

Identifying Every Group-Maximum Row

SELECT *
FROM (
  SELECT e.*,
         MAX(salary) OVER (
           PARTITION BY department
         ) AS department_maximum
  FROM employees e
) compared
WHERE salary = department_maximum;

Every tie is retained. This can be extended with other window metrics without a separate grouped join-back.

Running Maximum

SELECT measurement_time,
       value,
       MAX(value) OVER (
         ORDER BY measurement_time, measurement_id
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW
       ) AS running_maximum
FROM measurements;

A running maximum can stay unchanged or increase as rows are added; it cannot decrease. A deterministic tie-breaker ensures reproducible row order.

Moving Maximum

SELECT measurement_time,
       value,
       MAX(value) OVER (
         ORDER BY measurement_time
         ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
       ) AS moving_maximum
FROM measurements;

This is the maximum of five rows, not necessarily five minutes or dates. Build one row per intended period or use an appropriate duration-based design.

Conditional MAX()

SELECT MAX(CASE
         WHEN status = 'Completed' THEN total_amount
       END) AS largest_completed_order,
       MAX(CASE
         WHEN status = 'Pending' THEN total_amount
       END) AS largest_pending_order
FROM orders;

Nonmatching rows return NULL and are ignored. Conditional aggregation calculates related maximums from one shared input population.

Latest Successful Event

SELECT user_id,
       MAX(CASE
         WHEN event_status = 'SUCCESS' THEN event_time
       END) AS latest_success_time
FROM events
GROUP BY user_id;

Users without successful events receive NULL. The pattern supports latest pass, latest completed order, last successful login, or newest approved transaction.

Boolean-Like Conditional MAX

SELECT customer_id,
       MAX(CASE
         WHEN status = 'Cancelled' THEN 1 ELSE 0
       END) AS has_cancelled_order
FROM orders
GROUP BY customer_id;

A result of 1 means at least one cancellation exists; zero means none among the input rows. EXISTS can express a single yes/no check more directly, while conditional MAX is useful alongside grouped metrics.

First and Latest Together

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order,
       DATEDIFF(
         MAX(order_date),
         MIN(order_date)
       ) AS activity_span_days
FROM orders
GROUP BY customer_id;

This describes the observed order range. One order yields a zero-day span, not necessarily a zero-length relationship.

Outer Joins and Empty Groups

SELECT c.category_id,
       MAX(p.price) AS maximum_price
FROM categories c
LEFT JOIN products p
  ON p.category_id = c.category_id
 AND p.active = TRUE
GROUP BY c.category_id;

All categories appear. A category without active products has NULL maximum. Placing the active predicate in WHERE would remove empty categories.

Duplicates and DISTINCT

Duplicate equal values do not change a maximum. For 10, 20, 30, 30, and 30, both MAX(value) and MAX(DISTINCT value) return 30. DISTINCT is normally unnecessary and can obscure intent.

Join Multiplication

Repeating the same value through a one-to-many join may not change MAX, but an incorrect join can introduce different values or change eligibility. Always verify the actual joined population and grain.

MAX(id) Is Not Necessarily Latest

Identifiers can be imported, generated out of order, allocated in batches, or unrelated to event time. Use MAX(created_at) for latest creation. Use MAX(id) only when the data contract guarantees that larger identifiers represent the required chronology.

Latest Before and First After a Cutoff

SELECT MAX(event_time) AS latest_before_cutoff
FROM events
WHERE event_time < :cutoff_time;

SELECT MIN(event_time) AS first_after_cutoff
FROM events
WHERE event_time >= :cutoff_time;

These are complementary boundary queries. "Latest before" requires MAX; "first after" requires MIN.

Current Effective Record

Versioned data often needs the latest effective start that is not after an as-of time.

SELECT *
FROM price_version
WHERE product_id = :product_id
  AND effective_start <= :as_of_time
  AND (effective_end > :as_of_time
       OR effective_end IS NULL)
ORDER BY effective_start DESC, version_id DESC
LIMIT 1;

The current record is not simply the global MAX start; eligibility under the effective interval matters.

MAX and Range Analysis

SELECT MIN(salary) AS minimum_salary,
       MAX(salary) AS maximum_salary,
       MAX(salary) - MIN(salary) AS salary_range
FROM employees;

The range measures spread between extremes but says nothing about distribution inside them. Add average, percentiles, and counts where needed.

Data-Quality Checks with MAX()

A maximum can expose impossible values: rating above five, percentage above 100, timestamp in the distant future, quantity beyond capacity, or duration exceeding a service rule.

SELECT MAX(rating) AS maximum_rating,
       MAX(percent_value) AS maximum_percentage,
       MAX(created_at) AS latest_record
FROM imported_data;

Use CHECK constraints and validation to prevent invalid values; MAX is a profiling and monitoring tool, not a replacement for integrity rules.

Monitoring with Maximum Latency

MAX reveals the worst observed case and is sensitive to one anomaly. That is useful for incident investigation but can overstate typical experience. Segment by endpoint, status, payload, region, and cache behavior, then report count and percentiles alongside MAX.

Maximum Threshold Alerts

SELECT device_id,
       MAX(measured_value) AS period_maximum
FROM measurements
WHERE measured_at >= :period_start
  AND measured_at <  :period_end
GROUP BY device_id
HAVING MAX(measured_value) > :alert_ceiling;

This supports temperature, pressure, latency, balance, or capacity alerts. Normalize units and validate sensor quality first.

Security and Tenant Scope

Maximum queries must apply tenant and row-level authorization before aggregation. Even one value can reveal sensitive information such as highest salary or latest confidential event.

SELECT MAX(salary) AS tenant_maximum_salary
FROM employees
WHERE tenant_id = :tenant_id
  AND department_id IN (:authorized_departments);

Cached and precomputed maxima must preserve the same security scope. Privacy policies may suppress results for very small groups.

Concurrency and Latest Work Items

Finding the highest-priority or latest queued row and updating it later can allow multiple workers to claim the same item. MAX identifies a candidate but does not reserve it.

Use a transaction, row-locking pattern, atomic status update, or queue service supported by the database. Correct concurrency belongs to the workflow.

MAX() Performance

Performance depends on indexes, filters, grouping, joins, table size, partitions, transaction visibility, and the execution plan. Some engines can locate an indexed unfiltered maximum from an index edge without scanning every row.

Filtered and grouped maxima need index layouts matching the query. Verify with EXPLAIN rather than assuming MAX is always instant.

Composite Index for a Filtered Maximum

SELECT MAX(order_date)
FROM orders
WHERE customer_id = 101;

An index beginning with customer ID and followed by order date may help locate the latest order efficiently. Confirm against data distribution and the full workload.

Partitioned Tables

Date predicates can let a partitioned table prune irrelevant partitions before MAX is calculated. A global latest value may still need metadata or index work across partitions. Local versus global indexes and partition maintenance affect the plan.

Precomputed Maximums

Higher-level maxima can be rolled up as MAX of compatible lower-level maxima. Retain the source identifier and observation time so an extreme can be investigated.

Late larger values must update summaries. Deleting or correcting the row that supplied a stored maximum requires rediscovering the next-largest detail value, so pipelines need reconciliation or targeted recomputation.

Transaction Visibility

MAX returns the greatest value visible under the current transaction isolation. Concurrent inserts and uncommitted changes can make two sessions observe different latest values. Reproducible reports should identify a snapshot or cutoff.

Cross-Database Portability

Numeric and date MAX are broadly portable, but collations, timestamp behavior, time zones, NULL ordering in ORDER BY alternatives, top-row syntax, window support, and optimizer strategies vary. Test migration behavior rather than translating syntax mechanically.

Real-World Dashboard Summary

SELECT COUNT(*) AS order_count,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS average_order,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
WHERE status = 'Completed'
  AND order_date >= :period_start
  AND order_date <  :period_end;

All metrics share the same population. If amount can be NULL, row count and expression aggregates have different contributing counts.

MAX() Across Derived Business Values

The stored column is not always the value the business wants to maximize. A sales table may store quantity, unit price, discount, tax, and shipping separately, while the question asks for the largest final order value. In that case, the aggregate must receive the same expression used by the business calculation. Maximizing only unit_price would identify the most expensive single item, not the largest order.

SELECT MAX(
         quantity * unit_price
         - discount_amount
         + tax_amount
         + shipping_amount
       ) AS largest_final_total
FROM orders
WHERE status = 'Completed';

Derived expressions also require careful NULL handling. If discount_amount is nullable, ordinary arithmetic can turn the whole expression into NULL and silently remove that row from MAX. Use COALESCE(discount_amount, 0) only when the business definition truly treats a missing discount as zero. The goal is not merely to make the query run; it is to preserve the intended meaning.

Maximum of Multiple Columns in One Row

MAX is an aggregate across rows. It does not normally compare several columns within a single row. Suppose a service record contains cpu_time_ms, io_time_ms, and network_time_ms, and the requirement is to identify the largest component for each request. Some databases provide GREATEST() for that row-level comparison:

SELECT request_id,
       GREATEST(cpu_time_ms, io_time_ms, network_time_ms)
         AS largest_component_ms
FROM request_metrics;

To find the largest component observed anywhere, the row-level result can then be aggregated: MAX(GREATEST(...)). Portability and NULL behavior vary by database, so a CASE expression or an unpivot operation may be safer in shared SQL. Distinguishing row-wise comparison from column aggregation prevents a common design error.

MAX() with JSON-Derived Values

Modern databases often store semi-structured attributes in JSON. A numeric property extracted from JSON may be returned as text, and text comparison produces incorrect numeric maxima. For example, the string '9' can sort after '100'. Extract the property, validate its shape, and cast it to the intended type before applying MAX.

SELECT MAX(CAST(payload->>'responseTimeMs' AS INTEGER))
         AS slowest_response_ms
FROM api_events
WHERE payload ? 'responseTimeMs';

The exact extraction syntax differs among PostgreSQL, MySQL, SQL Server, and Oracle. Invalid JSON values, empty strings, and out-of-range numbers should be handled deliberately. Frequently queried properties are better represented by generated columns or normalized fields that can be typed, constrained, and indexed.

Maximum Per Time Bucket

Operational reports frequently need a maximum for each hour, day, or month rather than one extreme for the whole history. The timestamp must be transformed to a bucket using database-specific date functions, and that same bucket must appear in GROUP BY.

SELECT CAST(observed_at AS DATE) AS observation_day,
       MAX(response_time_ms) AS daily_peak_ms
FROM api_metrics
WHERE observed_at >= :start_time
  AND observed_at <  :end_time
GROUP BY CAST(observed_at AS DATE)
ORDER BY observation_day;

Time zones matter when a timestamp is stored in UTC but reports follow a local business day. Convert into the reporting zone before deriving the date, and document how daylight-saving transitions are treated. Half-open boundaries keep adjacent report periods from overlapping.

Maximum of Daily Maximums

A second aggregation can answer which daily peak was greatest during a reporting period. A common table expression makes the two grains explicit: first one row per day, then one maximum across those daily rows.

WITH daily_peaks AS (
  SELECT CAST(observed_at AS DATE) AS observation_day,
         MAX(response_time_ms) AS peak_ms
  FROM api_metrics
  WHERE observed_at >= :start_time
    AND observed_at <  :end_time
  GROUP BY CAST(observed_at AS DATE)
)
SELECT MAX(peak_ms) AS highest_daily_peak
FROM daily_peaks;

This two-stage form is valuable when another calculation is performed at the daily grain before the final maximum. It also makes review easier because each intermediate result has a clear meaning and can be tested independently.

Maximum Consecutive Sequence

Some requirements sound like MAX questions but require earlier sequence analysis. The longest run of failed builds, consecutive login days, or uninterrupted outage duration cannot be obtained by maximizing one raw column. The query must first identify islands of consecutive events, calculate each island's length or duration, and then apply MAX to those derived groups.

This distinction is important in interviews and production work: MAX selects the greatest value from the rows it receives; it does not infer sequences, relationships, or business episodes. When a requirement says “longest streak,” “largest gap,” or “most time without success,” derive the relevant unit first.

MAX() in Correlated Subqueries

A correlated subquery can retrieve rows matching the maximum within each outer group. Although expressive, it may execute less efficiently than a window function or a grouped join, depending on the optimizer and available indexes.

SELECT o.*
FROM orders o
WHERE o.order_date = (
  SELECT MAX(i.order_date)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

This returns all orders tied at the latest timestamp for a customer. If exactly one row is required, add a documented secondary rule through ranking rather than assuming timestamps are unique. Compare execution plans on representative data before choosing the correlated form for a high-volume path.

Maximum Values After Deduplication

Deduplication can change the population even though duplicate values alone do not change a simple maximum. Suppose an ingestion pipeline stores several versions of the same event and later versions can correct the measured value. MAX across all physical versions may retain an obsolete high reading. Select the authoritative version first, then calculate the maximum.

WITH ranked_events AS (
  SELECT e.*,
         ROW_NUMBER() OVER (
           PARTITION BY event_id
           ORDER BY revision_number DESC, loaded_at DESC
         ) AS revision_rank
  FROM event_history e
)
SELECT MAX(measured_value) AS current_maximum
FROM ranked_events
WHERE revision_rank = 1;

The same principle applies to soft-deleted records, superseded prices, and slowly changing dimensions. “All stored rows” and “current valid facts” are different datasets.

Presenting a Maximum Responsibly

An isolated maximum can be dominated by one data error or rare outlier. Dashboards should usually show context such as record count, average, percentile, observation time, and the identifier of the extreme row. A maximum latency of 90 seconds means something different when it came from one malformed request than when hundreds of requests were similarly slow.

Label the time range, filters, units, timezone, and whether the value is measured, estimated, or capped. If privacy rules restrict displaying the associated customer or employee, return a permitted reference or aggregate context instead of exposing sensitive details.

Review Scenario: Highest Current Product Price

Consider a product catalog with historical prices, scheduled future prices, disabled products, multiple currencies, and tenant ownership. “Highest product price” is ambiguous until those dimensions are defined. A defensible query might mean the highest currently effective USD price among active products for one tenant.

SELECT MAX(p.price_amount) AS highest_current_usd_price
FROM product_prices p
JOIN products x ON x.product_id = p.product_id
WHERE x.tenant_id = :tenant_id
  AND x.status = 'Active'
  AND p.currency_code = 'USD'
  AND p.effective_from <= :as_of_time
  AND (p.effective_to > :as_of_time OR p.effective_to IS NULL);

Before relying on this result, verify that overlapping price periods are forbidden or resolved, currency conversion is not required, NULL prices have a defined meaning, and the as-of timestamp is consistent across the report. This review shows why correct MAX usage depends as much on defining the eligible population as on writing the aggregate itself.

Common Mistakes

  1. Expecting MAX to return the complete associated row.
  2. Selecting unrelated columns beside MAX without valid grouping.
  3. Using MAX(id) as a substitute for the latest timestamp.
  4. Maximizing end time when the requirement is longest duration.
  5. Replacing NULL with zero before MAX on all-negative data.
  6. Confusing largest signed value with largest absolute magnitude.
  7. Using text MAX for semantic versions.
  8. Returning one tied row without a deterministic tie-breaker.
  9. Using incompatible units, currencies, collations, or time semantics.
  10. Assuming MAX alone describes a distribution.
  11. Ignoring population changes introduced by joins and filters.
  12. Selecting a latest work item without concurrency protection.

Testing MAX Queries

Test empty input, all-NULL values, zero, all-negative values, duplicates, ties, extreme values, string case and accents, date boundaries, future timestamps, and fractional precision. Verify whether all ties or exactly one deterministic row is required.

For grouped maxima, include empty groups through an outer join. For windows, test partition changes, ordering ties, and frame edges. Check execution plans with representative indexes and volumes.

Production Checklist

  1. Define exactly what should be maximized.
  2. Use the expression that represents the business quantity.
  3. Distinguish the maximum value from its complete row.
  4. Decide whether ties must be preserved.
  5. Use a deterministic tie-breaker for one-row results.
  6. Confirm NULL, zero, and negative-value semantics.
  7. Normalize units, currencies, collations, and time conventions.
  8. Use GROUP BY for one maximum per category.
  9. Restore correct grain after joins.
  10. Use explicit frames for running and moving maxima.
  11. Validate indexes and plans for important queries.
  12. Record filters, cutoff, snapshot, and source row.

Interview Questions and Answers

What is MAX()? It is an aggregate function that returns the largest non-NULL expression value.

Does MAX ignore NULL? Yes. If no non-NULL value exists, the result is NULL.

Can MAX work with dates? Yes; it returns the latest non-NULL date or timestamp.

How do you return all rows tied for the maximum? Filter detail rows against a MAX subquery using identical eligibility conditions.

How do you find a maximum per group? GROUP BY the category and apply MAX to each group.

MAX versus ORDER BY DESC LIMIT? MAX returns a value; ordered LIMIT returns one row under a tie policy.

Why is DISTINCT usually unnecessary? Duplicate equal values do not change the maximum.

How do you calculate a running maximum? Use MAX as an ordered window function with an explicit frame.

Why can MAX(id) be wrong for latest? Identifier order may not represent business event time.

Mental Model

For prices 50, 20, NULL, 100, and 35, the non-NULL values are 50, 20, 100, and 35. COUNT(price) is 4, SUM(price) is 205, AVG(price) is 51.25, MIN(price) is 20, and MAX(price) is 100.

FunctionQuestionResult
COUNT()How many known values?4
SUM()What is the total?205
AVG()What is the average?51.25
MIN()What is the smallest?20
MAX()What is the largest?100

Key Takeaway

MAX() answers "what is the largest, highest, slowest, longest, or latest non-NULL value?" It can operate globally, per group, conditionally, or over a window.

RELIABLE MAXIMUM = CORRECT EXPRESSION
                   + DEFINED POPULATION
                   + NULL AWARENESS
                   + COMPATIBLE COMPARISON RULES
                   + EXPLICIT TIE POLICY
                   + CORRECT RESULT GRAIN
                   + DETERMINISTIC ORDERING
                   + VERIFIED INDEX PLAN

Use MAX directly when only the value is needed, a subquery or rank when complete rows are required, GROUP BY for category maxima, and window MAX for row-level comparisons and running analysis. Always maximize the expression that represents the actual business requirement.