Skip to main content

MIN(), MAX()

Learning Focus

Use this lesson to understand MIN(), MAX() with practical syntax and examples.

Concept Overview​

MIN() and MAX() are aggregate functions that return the smallest and largest value in a set.

You will use them to answer questions like:

  • What is the lowest product price?
  • What is the highest salary in a department?
  • What is the earliest or latest event timestamp?

Why is it important?​

  • Fast insight: extreme values are common KPIs (first/last, lowest/highest)
  • Data quality: range checks detect outliers and bad imports
  • Operational monitoring: freshness checks (latest event time) and SLA checks (max latency)

Where does it fit?​

MIN() and MAX() are part of aggregate analysis, often combined with GROUP BY (per group) and HAVING (filter groups). When you need the row associated with the min/max value, you typically use ORDER BY ... LIMIT or PostgreSQL's DISTINCT ON.

Syntax & Rules​

Core Syntax​

-- One result value
SELECT MIN(col) AS min_value, MAX(col) AS max_value
FROM table_name;

-- One result per group
SELECT group_key, MIN(col) AS min_value, MAX(col) AS max_value
FROM table_name
GROUP BY group_key;

Available Options / Parameters​

PatternWhat it doesNotes
MIN(expr) / MAX(expr)compute extremesexpr can be a column or expression
FILTER (WHERE ...)conditional aggregatesPostgreSQL feature: MAX(x) FILTER (WHERE ...)
GROUP BYper-group extremesreturns one row per group
ORDER BY ... LIMITget the row with min/maxuse a tie-breaker for deterministic results
DISTINCT ON (key)per-group “top row”PostgreSQL-native, great for “max row per group”

Key Rules and Considerations​

  • MIN() and MAX() ignore NULLs.
  • They work on numeric, date/time, and text types (text uses sort order/collation).
  • MIN()/MAX() return a value, not the full row. Use other patterns when you need associated columns.
  • If you need deterministic results (ties), add a stable tie-breaker column (often the primary key).

Step-by-Step Examples​

Example 1: Simple MIN/MAX (Beginner)​

CREATE TABLE products (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  price      numeric(10,2) NOT NULL CHECK (price >= 0)
);

INSERT INTO products (name, price) VALUES
  ('Mouse', 9.99),
  ('Keyboard', 24.99),
  ('Monitor', 199.99),
  ('Headset', 49.50);

SELECT
  MIN(price) AS lowest_price,
  MAX(price) AS highest_price
FROM products;

Expected output:

 lowest_price | highest_price
--------------+--------------
 9.99         | 199.99
(1 row)

Explanation:

  • This summarizes the full table into a single row.

Example 2: MAX per Group (Intermediate)​

CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL,
  salary      numeric(12,2) NOT NULL CHECK (salary >= 0)
);

INSERT INTO employees (full_name, department, salary) VALUES
  ('Alice', 'Engineering', 100000.00),
  ('Bob', 'Engineering', 120000.00),
  ('Charlie', 'Sales', 85000.00),
  ('Diana', 'HR', 65000.00),
  ('Eve', 'Sales', 79000.00);

SELECT
  department,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY department;

Expected output:

 department  | max_salary
-------------+-----------
 Engineering | 120000.00
 HR          | 65000.00
 Sales       | 85000.00
(3 rows)

Explanation:

  • GROUP BY department produces one output row per department.

Example 3: Get the Row with the MAX Value (Subquery) (Intermediate)​

SELECT employee_id, full_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees)
ORDER BY employee_id;

Expected output:

 employee_id | full_name | salary
-------------+-----------+----------
 2           | Bob       | 120000.00
(1 row)

Explanation:

  • This pattern returns all ties (if multiple employees share the max salary).

Example 4: Per-Department “Top Earner Row” with DISTINCT ON (Advanced)​

If you want one row per group (and you want to keep other columns), DISTINCT ON is a PostgreSQL-native solution.

-- One row per department: highest salary, tie-break by employee_id
SELECT DISTINCT ON (department)
  department,
  employee_id,
  full_name,
  salary
FROM employees
ORDER BY department, salary DESC, employee_id;

Expected output:

 department  | employee_id | full_name | salary
-------------+-------------+-----------+----------
 Engineering | 2           | Bob       | 120000.00
 HR          | 4           | Diana     | 65000.00
 Sales       | 3           | Charlie   | 85000.00
(3 rows)

Explanation:

  • DISTINCT ON (department) keeps the first row per department according to the ORDER BY.
  • Always include a tie-breaker (employee_id) to make the chosen row deterministic.

Example 5: NULLs Are Ignored (and How to Handle Them) (Advanced)​

CREATE TABLE reviews (
  review_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint NOT NULL,
  score integer
);

INSERT INTO reviews (product_id, score) VALUES
  (1, 5),
  (1, NULL),
  (1, 3),
  (2, NULL);

SELECT
  product_id,
  MIN(score) AS min_score,
  MAX(score) AS max_score
FROM reviews
GROUP BY product_id
ORDER BY product_id;

Expected output:

 product_id | min_score | max_score
------------+-----------+-----------
 1          | 3         | 5
 2          |           |
(2 rows)

Explanation:

  • For product_id = 2, there are no non-NULL scores, so both results are NULL.

Practical Use Cases​

1) Data freshness checks​

SELECT MAX(created_at) AS latest_event
FROM events;

2) SLA and latency monitoring​

SELECT MAX(response_ms) AS worst_response
FROM api_requests
WHERE occurred_at >= now() - interval '1 hour';

3) Inventory control​

SELECT MIN(quantity_on_hand) AS lowest_stock
FROM inventory;

4) Range validation (spot outliers)​

SELECT MIN(price) AS min_price, MAX(price) AS max_price
FROM products;

5) “Top row per group” reporting​

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  total_amount,
  created_at
FROM orders
ORDER BY customer_id, created_at DESC, order_id DESC;

Common Mistakes & Troubleshooting​

1) Using MIN/MAX in WHERE incorrectly​

Wrong SQL:

SELECT MIN(price)
FROM products
WHERE MIN(price) > 100;

Bad outcome:

  • Aggregates cannot be used like that in WHERE.

Fix (filter rows before aggregating):

SELECT MIN(price)
FROM products
WHERE price > 100;

2) Forgetting GROUP BY​

Wrong SQL:

SELECT department, MAX(salary)
FROM employees;

Typical error:

ERROR:  column "employees.department" must appear in the GROUP BY clause or be used in an aggregate function

Fix:

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

3) Expecting NULLs to be included​

Wrong mental model:

  • Expecting MIN()/MAX() to return NULL when NULL is present.

Fix:

  • Remember: NULLs are ignored. If you need special behavior, use explicit logic (for example, COUNT(score) to detect missing values).

4) Confusing MAX(value) with “row of max value”​

Wrong approach:

SELECT MAX(salary), full_name
FROM employees;

Bad outcome:

  • This does not reliably return the name associated with the max salary.

Fix:

SELECT full_name, salary
FROM employees
ORDER BY salary DESC, employee_id
LIMIT 1;

Debugging checklist:

  1. Confirm whether you need a value (MAX(salary)) or the row with that value.
  2. If using GROUP BY, ensure every non-aggregated column is grouped.
  3. Check for NULLs and decide whether ignoring them is correct.
  4. If you need a deterministic “top row”, add a tie-breaker.
  5. Use EXPLAIN if performance is a concern (especially for large tables).

Best Practices​

  • ✅ Alias aggregate outputs (MAX(salary) AS max_salary) for clarity. ❌ Avoid unlabeled aggregates in reporting queries.
  • ✅ Use GROUP BY when you select grouping keys. ❌ Avoid selecting non-aggregated columns without grouping.
  • ✅ Use DISTINCT ON or ORDER BY ... LIMIT to fetch the row associated with a min/max value. ❌ Avoid mixing aggregates and non-aggregates expecting “matching row” behavior.
  • ✅ Add deterministic tie-breakers (ORDER BY value DESC, id). ❌ Avoid nondeterministic results when multiple rows share the same extreme.
  • ✅ Treat NULL behavior explicitly (ignored by default). ❌ Avoid assuming NULLs affect MIN/MAX.

Hands-On Practice​

Use this setup for the exercises:

CREATE TABLE practice_products (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  price      numeric(10,2) NOT NULL
);

INSERT INTO practice_products (name, price) VALUES
  ('Mouse', 9.99),
  ('Keyboard', 24.99),
  ('Monitor', 199.99),
  ('Headset', 49.50);

Exercise 1 (Easy): Find min and max price​

Task: Return the lowest and highest price.

-- Your SQL here

Solution:

SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price
FROM practice_products;

Exercise 2 (Medium): Max per group​

Task: Create a small employees table and return max salary per department.

-- Your SQL here

Solution:

CREATE TABLE practice_employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department  text NOT NULL,
  salary      numeric(12,2) NOT NULL
);

INSERT INTO practice_employees (department, salary) VALUES
  ('Engineering', 100000.00),
  ('Engineering', 120000.00),
  ('Sales', 85000.00);

SELECT department, MAX(salary) AS max_salary
FROM practice_employees
GROUP BY department
ORDER BY department;

Exercise 3 (Advanced): Get the row with the max value​

Task: Return the product row with the highest price (deterministic tie-break).

-- Your SQL here

Solution:

SELECT product_id, name, price
FROM practice_products
ORDER BY price DESC, product_id
LIMIT 1;

Connection to Other Concepts​

ConceptWhy it matters
COUNT/AVG/SUMother common aggregates in the same workflow
GROUP BYproduces per-group min/max results
HAVINGfilters groups based on aggregate values
ORDER BY / LIMITcommon pattern to retrieve the row with the extreme
NULL ValuesMIN/MAX ignore NULLs, which affects results
DISTINCT ONPostgreSQL-native way to pick top row per group

Visual Learning Diagram​

flowchart TD
  A[SELECT] --> B[GROUP BY]
  B --> C[Aggregate Functions]
  C --> D[MIN / MAX]
  D --> E[Per-Group Extremes]
  D --> F[Row With Extreme]
  F --> G[ORDER BY + LIMIT]
  F --> H[DISTINCT ON]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class D highlight

Common Pitfalls​

PitfallConsequencePrevention
Expecting MIN/MAX to include NULLsmisleading conclusionsremember NULLs are ignored; check missingness separately
Selecting non-aggregated columns with aggregateserrors or wrong expectationsuse GROUP BY or use row-retrieval patterns
Using MAX(value) and expecting matching row columnsincorrect associationuse ORDER BY ... LIMIT or DISTINCT ON
No tie-breaker for “top row”nondeterministic choiceadd a stable tie-breaker column
Text MIN/MAX used as “best value”surprising orderinguse explicit business keys instead of collation order

Quick Reference​

SELECT MIN(col), MAX(col) FROM t;
SELECT key, MAX(col) FROM t GROUP BY key;
SELECT * FROM t ORDER BY col DESC, id LIMIT 1;
SELECT DISTINCT ON (key) * FROM t ORDER BY key, col DESC, id;
SELECT MAX(col) FILTER (WHERE status = 'paid') FROM t;

What's Next​