How to Find the Maximum Value of a Numeric Column in SQL
Database:
Operators:
Table of Contents
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.
| id | name | year | items |
|---|---|---|---|
| 1 | bread roll | 2018 | 345 |
| 2 | chocolate | 2017 | 123 |
| 3 | butter | 2019 | 34 |
| 4 | bread roll | 2019 | 456 |
| 5 | butter | 2018 | 56 |
| 6 | butter | 2017 | 78 |
| 7 | chocolate | 2019 | 87 |
| 8 | chocolate | 2018 | 76 |
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.
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:
| year | max_items |
|---|---|
| 2018 | 345 |
| 2017 | 123 |
| 2019 | 456 |