What is SQLite MAX aggregate function?

SQLite's MAX aggregate function returns the maximum value from a set of values. It can be applied in various contexts, including within SELECT statements, and is particularly useful for identifying the highest value among a series of numbers, such as when handling grades, sales data, or any dataset requiring comparison.

For example, if we have a table named Sales with two fields, Year and Revenue, we can use the MAX function to find the highest revenue in the records. The SQL query is as follows:

sql
SELECT MAX(Revenue) FROM Sales;

This statement returns the maximum value of the Revenue column in the Sales table.

Additionally, the MAX function can be combined with the GROUP BY clause to find the maximum value within each group. Suppose in the above Sales table, we also want to view the highest revenue for each year; we can write a query like this:

sql
SELECT Year, MAX(Revenue) 
FROM Sales 
GROUP BY Year;

This statement returns the highest Revenue for each year present in the Year column. It is highly valuable for analyzing annual performance peaks.