Back to cookbooks list Articles Cookbook

How to Find the Maximum Value of a Numeric Column in SQL

  • MAX

Problem:

You’d like to find the maximum value of a numeric column.

Example:

Our database has a table named product with data in the following columns: id, name, year, and items.

idnameyearitems
1bread roll2018345
2chocolate2017123
3butter201934
4bread roll2019456
5butter201856
6butter201778
7chocolate201987
8chocolate201876

Let’s find the maximum number of items sold over all years.

Solution:

SELECT MAX(items) as max_items
FROM product;

Here’s the result:

max_items
456

Discussion:

To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table. In our example, the query returns the maximum number among all items.

Discover the best interactive SQL courses

Of course, since it’s an aggregate function, MAX() can also be used with groups. For example, if we’d like to see the maximum number of items sold in each year, we can write this query:

SELECT year, MAX(items) AS max_items
FROM product
GROUP BY year;

The maximum is calculated for each group:

yearmax_items
2018345
2017123
2019456

Recommended courses:

Recommended articles:

See also: