PostgreSQL MIN and MAX Functions

The MIN() and MAX() functions in PostgreSQL return the minimum and maximum values from a set of values respectively. They work with numeric, text, and date/time data types.

Syntax

-- Find minimum value
MIN(column_name)

-- Find maximum value
MAX(column_name)

-- With DISTINCT (has no effect, included for completeness)
MIN(DISTINCT column_name)
MAX(DISTINCT column_name)

-- With FILTER clause
MIN(column_name) FILTER (WHERE condition)
MAX(column_name) FILTER (WHERE condition)

Official Documentation

Example usage

-- Find minimum and maximum prices
SELECT
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

-- MIN/MAX with GROUP BY
SELECT
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Find earliest and latest dates
SELECT
    MIN(created_at) AS first_order,
    MAX(created_at) AS latest_order,
    AGE(MAX(created_at), MIN(created_at)) AS time_span
FROM orders;

-- MIN/MAX with text values
SELECT
    MIN(name) AS first_alphabetically,
    MAX(name) AS last_alphabetically
FROM customers;

-- Conditional MIN/MAX using FILTER
SELECT
    MIN(price) AS overall_min,
    MIN(price) FILTER (WHERE category = 'Electronics') AS electronics_min,
    MAX(price) FILTER (WHERE category = 'Electronics') AS electronics_max
FROM products;

-- Find records with minimum/maximum values
SELECT *
FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- Multiple aggregations
SELECT
    user_id,
    COUNT(*) AS order_count,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id;

-- Range calculation
SELECT
    category,
    MAX(price) - MIN(price) AS price_range,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

-- Using with window functions
SELECT
    product_name,
    price,
    category,
    MAX(price) OVER (PARTITION BY category) AS max_in_category
FROM products;

Common issues

1. NULL handling: MIN and MAX ignore NULL values. If all values are NULL, returns NULL.
2. Empty result sets: Returns NULL for empty sets, not 0.
3. String comparison: MIN/MAX on text uses alphabetical ordering based on collation.
4. DISTINCT has no effect: MIN(DISTINCT col) = MIN(col) since duplicate values don't affect min/max.

Best practices

1. Create indexes on columns frequently used in MIN/MAX queries for better performance.
2. Use FILTER clause for conditional min/max instead of subqueries.
3. For finding rows with min/max values, consider using window functions or DISTINCT ON.
4. Be aware of collation when using MIN/MAX on text columns.

Frequently Asked Questions

Q: How do MIN and MAX handle NULL values?
A: Both functions ignore NULL values. If all values in the set are NULL, they return NULL. For example, MAX(5, NULL, 3) returns 5.

Q: Can I use MIN and MAX on text/string columns?
A: Yes, MIN and MAX work on text columns using alphabetical ordering. MIN returns the first alphabetically, MAX returns the last, based on the database collation.

Q: How do I get the entire row with the maximum value?
A: Use a subquery: SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products) or use DISTINCT ON: SELECT DISTINCT ON (1) * FROM products ORDER BY price DESC.

Q: What's the difference between MAX and GREATEST?
A: MAX is an aggregate function that finds the maximum across multiple rows. GREATEST is a regular function that finds the maximum among multiple values in a single row: GREATEST(col1, col2, col3).

Q: Does DISTINCT affect MIN or MAX results?
A: No, MIN(DISTINCT column) produces the same result as MIN(column) because duplicate values don't change the minimum or maximum value.