SQL MAX

What is SQL MAX?

Returns the maximum non-NULL value from a set of values.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MAX: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and all ANSI-compliant databases

SQL MAX Full Explanation

MAX is a standard SQL aggregate and analytic (window) function that scans a collection of values and returns the highest non-NULL value it encounters. When used as an aggregate, it collapses multiple input rows into a single output row per grouping set. When paired with an OVER() clause, MAX becomes a window function that supplies the maximum value relative to the current row's window frame while preserving row granularity.Key points:- Operates on numeric, date/time, and string data types that support ordering.- Ignores NULL values. If all values are NULL, the result is NULL.- Accepts the DISTINCT modifier in some dialects, though it is redundant because the maximum of a set equals the maximum of its distinct set.- Works inside HAVING, ORDER BY, SELECT, and subqueries but cannot appear directly in a WHERE clause without a subquery because aggregates are evaluated after filtering.- Window usage supports PARTITION BY, ORDER BY, ROWS/RANGE frames, enabling running or sliding maximum calculations.- Collation rules dictate string ordering. Be cautious when comparing character data across different collations.- Performance depends on indexing and grouping. Aggregate MAX on an indexed column can be optimized to O(1) in many databases.

SQL MAX Syntax

-- Aggregate form
SELECT MAX([DISTINCT] expression) AS max_value
FROM   table_name
[WHERE  condition]
[GROUP BY grouping_columns];

-- Window form
SELECT expression,
       MAX(expression) OVER (PARTITION BY partition_cols
                             ORDER BY order_cols
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max
FROM   table_name;

SQL MAX Parameters

expression (Any comparable data type) - Value to evaluate for maximum.
DISTINCT (Keyword (optional)) - Eliminates duplicates before evaluation.
window_clause (Window definition) - Defines partitions and frame for window MAX.

Example Queries Using SQL MAX

-- 1. Highest order total across all orders
SELECT MAX(total_amount) AS highest_order
FROM   orders;

-- 2. Highest salary per department
SELECT department_id,
       MAX(salary) AS top_salary
FROM   employees
GROUP BY department_id;

-- 3. Running maximum of daily sales
SELECT sale_date,
       daily_total,
       MAX(daily_total) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak
FROM   daily_sales
ORDER BY sale_date;

Expected Output Using SQL MAX

A single row showing the greatest total_amount value.
One row per department with the highest salary in that department.
Each row displays the current day's total and the highest total seen up to that day.

Use Cases with SQL MAX

Find the highest transaction amount for fraud checks.
Determine the latest timestamp in event streams.
Select the most recent record in slowly changing dimensions.
Calculate running peak metrics in time-series analysis.
Identify top performers (e.g., highest sales by rep).

Common Mistakes with SQL MAX

Using MAX in WHERE instead of HAVING or a subquery.
Expecting NULLs to participate in comparison.
Applying MAX to mismatched data types causing implicit conversion errors.
Forgetting GROUP BY columns when selecting non-aggregated fields.
Assuming DISTINCT changes the result when duplicates exist.

First Introduced In

SQL-92

Frequently Asked Questions

Does MAX include NULL values?

No. The function skips NULLs. If every row is NULL, the result is NULL.

How do I find the highest salary in each department?

Group by department and apply MAX:SELECT department_id, MAX(salary) AS top_salary FROM employees GROUP BY department_id;

Can I use MAX in a WHERE clause?

Not directly. Use a subquery or HAVING. Example:SELECT * FROM orders WHERE total_amount = (SELECT MAX(total_amount) FROM orders);

Is DISTINCT necessary with MAX?

Almost never. MAX of a set equals the MAX of its distinct values, so DISTINCT is redundant unless required for syntax consistency.