SQL MIN Function: Find the Smallest Value with Examples

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

MIN(amount) reads the known numeric amounts and returns the lowest one. In this dataset those values are ten, thirty, twenty, twenty and five, so the answer is five. Sale 104 supplies NULL and is ignored for the comparison. If every selected amount is NULL, the result is NULL. An aggregate without GROUP BY still returns one summary row when its input is empty; MIN in that row is NULL rather than zero.

MIN can also apply to appropriate text and date values. The earliest created_on date is the minimum date. The smallest text value is determined by the column’s collation, so it should not be described as a universal dictionary order. Store prices as numeric values and dates with an appropriate date representation. Taking MIN over text that happens to look numeric can produce lexical rather than numeric ordering, depending on the engine and type.

GROUP BY customer_id creates a separate input group for each customer link. MIN(amount) then reports a minimum per group. The group for customer 3 contains an unknown amount and twenty, so its minimum is twenty. An anonymous customer_id group also exists because sale 106 has a missing link. Filtering customer_id IS NOT NULL excludes that group when the report is specifically about identified customers.

Do not select an unrelated id beside MIN(amount) without a valid grouping rule. Strict databases reject that expression; permissive ones may choose a value with engine-specific behaviour. To find every sale at the minimum, compare amount with a scalar subquery SELECT MIN(amount). To find exactly one cheapest sale, sort by amount and a unique tie-breaker, then use the engine’s row-limit syntax. These approaches express different requirements when prices tie. An index may help a minimum query, but performance depends on the filter, index layout and plan.

MIN as an aggregate is supported by all four systems. SQLite additionally allows a multiple-argument scalar min(...) whose behaviour differs from the one-argument aggregate. Do not use that extension when demonstrating grouped MIN. Type compatibility and text collation can vary across engines.

Check the engine-specific syntax and version requirements in MySQL MIN aggregate.

Return the smallest known amount for each identified customer. Exclude the anonymous customer link and order the summaries by customer id.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT customer_id,MIN(amount) AS smallest_amount
FROM sales WHERE customer_id IS NOT NULL
GROUP BY customer_id ORDER BY customer_id;

customer_id | smallest_amount
1 | 10
2 | 20
3 | 20

Customer 1 contributes ten and thirty, so its minimum is ten. Customer 2 has twenty. Customer 3’s unknown amount is skipped and its known twenty remains. Dev is absent because this query groups the sales input rather than starting from every customer.

Filter the source to paid sales and predict the same minimum five. Filter it to identified customers and predict ten because the anonymous five is excluded. Filter to sale 104 alone and observe NULL rather than zero. Finally query sale rows equal to the overall MIN to connect a summary value with its source identity without relying on a bare ungrouped id.