SQL MAX Function: Largest Values, Dates and Grouped Results

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.

MAX(amount) compares the known amounts and returns thirty in the supplied sales table. The missing amount in sale 104 does not compete. An unknown maximum is represented by NULL when no known value exists, including an aggregate over an empty filtered input. That distinction matters: zero can be a real maximum when all amounts are zero, while NULL means there was no known candidate.

GROUP BY changes the scope of the maximum. MAX(amount) over the whole table returns one value, while GROUP BY customer_id returns one maximum per customer link. A maximum is not a sum or a count: several smaller purchases never combine into a larger maximum. Customer 1’s ten and thirty produce thirty rather than forty. Use SUM for total purchasing value and keep the output alias clear enough that a reader can tell which measure is being reported.

MAX(created_on) returns the latest saved date in a comparable date column. It answers when the latest event was recorded, but not automatically which status or customer belonged to that event. If two sales share that date, both correspond to the maximum. Compare dates with a scalar MAX subquery to return all latest-date rows, or apply a deterministic ranking rule when exactly one latest record is needed. The same tie issue arises for maximum prices.

Several MAX expressions in one SELECT are independent summaries. MAX(amount) and MAX(created_on) can come from different records; placing them side by side does not describe one greatest sale. Avoid using this arrangement as a substitute for sorting or ranking. For text values, collation establishes what largest means. A code that contains digits is not necessarily a number, and the alphabetically largest code is not always the newest or highest numeric code. Choose types and ordering rules that match the business meaning.

Aggregate MAX is portable across these four engines. SQLite’s multiple-argument scalar max(...) is a separate form that should not be confused with the aggregate. Dates should use an appropriate representation: ISO-formatted text dates in SQLite sort coherently when the format is consistent, whereas arbitrary display strings do not.

Check the engine-specific syntax and version requirements in PostgreSQL MAX aggregate.

Find every sale whose amount equals the largest known amount. Return id and amount, sorted by id.

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

SELECT id,amount FROM sales
WHERE amount = (SELECT MAX(amount) FROM sales)
ORDER BY id;

id | amount
102 | 30

The scalar subquery returns thirty. The outer predicate compares each sale amount with that value, keeping sale 102. If another sale were thirty, both would appear. NULL does not equal the known maximum and therefore does not enter the result.

Compare MAX(amount) with MAX(created_on): the first is thirty and the second is February 3. Read their source rows to show that the values come from different transactions. Restrict to paid status and predict a maximum amount of twenty, then return every paid sale equal to that maximum to observe the two tied real records.