SQL MAX Function

Learn about the SQL MAX function, which is used to find the highest value in your dataset. This article explains how to use the syntax of MAX function and shows typical usage examples.

The SQL MAX function is an aggregate function that returns the maximum value in a column. MAX function, together with its counterpart MIN function, is useful for summarizing data in data analysis. It can help you to compute the range of values in a dataset.

The abstract syntax for using the MAX function is as follows:

SELECT MAX(column_name) 
FROM table_name;

The query will scan the given column in the table and return the highest value found.

To improve your understanding of SQL functions like MAX and MIN, explore our interactive SQL Basics course. It covers all the foundational topics of SQL in 129 practice exercises.

Examples of SQL MAX Function

Example 1: The Highest Price

SELECT
  MAX(price) AS highest_price
FROM products;

This query finds the maximum price of products in the products table.

For a detailed guide on this application, refer to How to Find the Maximum Value of a Numeric Column in SQL.

Example 2: When Was The Latest Product Launched?

MAX function does not have to be applied to number values. You can apply it to dates or text data. To determine the most recent release date in the products table:

SELECT
  MAX(release_date) AS latest_release
FROM products;

This query retrieves the latest date a product was released.

Example 3: The Last Product Alphabetically

Although primarily useful for numeric and date data, MAX can also be applied to text data.

To find the alphabetically last product name, use this query:

SELECT
  MAX(product_name) AS last_product_name
FROM products;

If you’re looking for more examples of MIN and MAX functions, check out our article SQL MIN and MAX Functions Explained in 6 Examples.

Combining MAX function with GROUP BY

The MAX function is typically used with the GROUP BY clause in SQL to find the maximum values in different groups of data. This is useful for analyzing data categorized into segments like product types or regions.

For practical applications of how GROUP BY works, check out our article 5 Examples of GROUP BY.

Example 1: The Highest Price in Each Category

To find the highest price in each product category:

SELECT
  category,
  MAX(price) AS max_price
FROM products
GROUP BY category;

Example 2: The Highest Price for Each Category and Manufacturer

For the highest price and most recent release by category and manufacturer:

SELECT
  category,
  manufacturer,
  MAX(price) AS max_price,
  MAX(release_date) AS latest_release
FROM products
GROUP BY category, manufacturer;

If you are looking for exercises to practice GROUP BY, see our article 10 GROUP BY SQL Practice Exercises with Solutions.

NULL Values with MAX Function

The MAX function in SQL ignores NULL values in a column when calculating the maximum value. Consider a products table with some NULL entries in the price column:

SELECT
  MAX(price) AS max_price
FROM products;

The MAX(price) computes the highest non-NULL price. If all prices are NULL, MAX returns NULL, indicating no data to calculate a maximum.

From MAX to More: Next Steps In SQL

In this guide we've covered how to use the SQL MAX function to find maximum values, work with GROUP BY, and handle NULL values. As you have seen, this function is essential for analyzing and extracting insights from data.

If you're new to SQL or want to improve your skills, consider checking out the SQL Basics course on LearnSQL.com. It's a great way to learn the basics. For more hands-on practice, the SQL Practice track offers practical exercises to help you master SQL.

These resources are excellent for boosting your SQL knowledge and skills, whether you're starting out or looking to brush up on your techniques.