SQLite MIN and MAX
MIN returns the smallest value in a column. MAX returns the largest. Both functions work on numbers, text, and dates — making them useful for finding extremes across a variety of data types.
Sample Table
products ────────────────────────────────────────────────────────── id │ name │ category │ price │ stock │ added_on ───┼───────────────┼─────────────┼────────┼───────┼────────── 1 │ Notebook │ Stationery │ 2.99 │ 50 │ 2023-01-15 2 │ Pen │ Stationery │ 0.99 │ 200 │ 2023-03-10 3 │ Backpack │ Bags │ 24.99 │ 15 │ 2023-06-01 4 │ Water Bottle │ Accessories │ 8.50 │ 30 │ 2022-11-20 5 │ Eraser │ Stationery │ 0.59 │ 75 │ 2023-02-05 6 │ Laptop Bag │ Bags │ 34.99 │ 8 │ 2024-01-10 7 │ Sticky Notes │ Stationery │ 1.99 │ 120 │ 2022-09-14
Basic MIN and MAX
-- Cheapest product SELECT MIN(price) AS cheapest FROM products; cheapest: 0.59 -- Most expensive product SELECT MAX(price) AS most_expensive FROM products; most_expensive: 34.99 -- Most stock, least stock SELECT MIN(stock) AS low_stock, MAX(stock) AS high_stock FROM products; low_stock: 8 high_stock: 200
Get the Product Name with MIN/MAX
MIN and MAX return values, not the full row. To see the name of the cheapest or most expensive product, use a subquery.
-- Name of the most expensive product SELECT name, price FROM products WHERE price = (SELECT MAX(price) FROM products); name │ price ───────────┼─────── Laptop Bag │ 34.99 -- Name of the cheapest product SELECT name, price FROM products WHERE price = (SELECT MIN(price) FROM products); name │ price ───────┼─────── Eraser │ 0.59
MIN and MAX on Text
On text columns, MIN returns the alphabetically first value and MAX returns the alphabetically last.
SELECT MIN(name) AS first_name, MAX(name) AS last_name FROM products; first_name │ last_name ──────────────┼────────────── Backpack │ Water Bottle (Alphabetical order: Backpack comes first, Water Bottle last)
MIN and MAX on Dates
On date columns stored as text in ISO 8601 format (YYYY-MM-DD), MIN returns the earliest date and MAX returns the most recent.
-- When was the first product added? SELECT MIN(added_on) AS first_added FROM products; first_added: 2022-09-14 (Sticky Notes) -- When was the most recent product added? SELECT MAX(added_on) AS last_added FROM products; last_added: 2024-01-10 (Laptop Bag)
MIN/MAX with GROUP BY
Combine MIN/MAX with GROUP BY to find the extreme value within each group.
SELECT category,
MIN(price) AS cheapest,
MAX(price) AS priciest
FROM products
GROUP BY category;
category │ cheapest │ priciest
────────────┼──────────┼─────────
Accessories │ 8.50 │ 8.50
Bags │ 24.99 │ 34.99
Stationery │ 0.59 │ 2.99
MIN/MAX and NULL
MIN and MAX ignore NULL values in their calculations.
TABLE snippet with a NULL price: id │ price ───┼─────── 1 │ 5.00 2 │ NULL 3 │ 12.00 SELECT MIN(price) FROM above; -- 5.00 (NULL ignored) SELECT MAX(price) FROM above; -- 12.00 (NULL ignored)
Practical Use: Low Stock Alert
-- Find the product with the least stock SELECT name, stock FROM products WHERE stock = (SELECT MIN(stock) FROM products); name │ stock ───────────┼─────── Laptop Bag │ 8 -- Find all products with stock below the average SELECT name, stock FROM products WHERE stock < (SELECT AVG(stock) FROM products) ORDER BY stock; name │ stock ─────────────┼─────── Laptop Bag │ 8 Backpack │ 15 Water Bottle │ 30
MIN vs ORDER BY LIMIT 1
-- These two produce the same cheapest price: SELECT MIN(price) FROM products; SELECT price FROM products ORDER BY price ASC LIMIT 1; -- Use MIN when you only need the value. -- Use ORDER BY LIMIT 1 when you need the full row.
Key Takeaways
MIN(col)returns the smallest value;MAX(col)returns the largest- Both work on numbers, text (alphabetical), and ISO date strings
- Both ignore NULL values
- To get the full row of the min/max item, use a subquery in WHERE
- With GROUP BY, MIN and MAX find extremes within each group