Lessons · SQL · the largest value
The largest value
MAX(col) returns the largest value in the column; MIN the smallest. Both ignore NULLs and return one row.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Latest order date, highest price, longest film: extremes are the questions people ask first about any column.
How to think about it
MAX for the value alone. For the row that holds it, sort and LIMIT 1, or use a window function; SELECT name, MAX(price) does not give the name of the priciest product.
Worked example
SELECT MAX(amount) FROM orders;The biggest single amount.
SELECT MIN(placed), MAX(placed) FROM orders;The first and last order dates: text dates in YYYY-MM-DD sort correctly.
SELECT title FROM films ORDER BY minutes DESC LIMIT 1;The row that holds the maximum, when you need the row.
Your turn
The cheapest price.
SELECT (price) FROM products;
Run a query against real tables
The trap
SELECT name, MAX(price) FROM products runs in SQLite and returns some name, not necessarily the one with the max price. Sort and LIMIT, or use a window.
Practise the largest value on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.