PostgreSQL MIN and MAX

Key Concepts

SELECT MIN(price) FROM products;

SELECT MAX(price) FROM products;

SELECT MIN(salary) FROM employees;

SELECT MAX(order_date) FROM orders;

🔹 Basic MIN and MAX

MIN returns the smallest value in a column, while MAX returns the largest. These aggregate functions scan all rows and return a single result. They automatically ignore NULL values, making them reliable for finding actual minimum and maximum values in your data.

-- Find cheapest product price
SELECT MIN(price) AS lowest_price 
FROM products;

-- Find most expensive product price
SELECT MAX(price) AS highest_price 
FROM products;

-- Find both in one query
SELECT 
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price 
FROM products;

🔹 MIN and MAX with Dates

MIN and MAX work perfectly with date and timestamp columns. MIN finds the earliest date, while MAX finds the most recent date. This is extremely useful for finding first and last orders, oldest and newest records, or date ranges in your data.

-- Find earliest order date
SELECT MIN(order_date) AS first_order 
FROM orders;

-- Find most recent order date
SELECT MAX(order_date) AS latest_order 
FROM orders;

-- Find date range
SELECT 
    MIN(order_date) AS first_order,
    MAX(order_date) AS latest_order,
    MAX(order_date) - MIN(order_date) AS days_span
FROM orders;

-- Find oldest and newest user registration
SELECT 
    MIN(created_at) AS first_user,
    MAX(created_at) AS newest_user 
FROM users;

🔹 MIN and MAX with Text

When used with text columns, MIN returns the alphabetically first value and MAX returns the alphabetically last value. The comparison is case-sensitive and based on character codes. This can be useful for finding alphabetical ranges or sorting boundaries in your data.

-- Find first and last names alphabetically
SELECT 
    MIN(name) AS first_alphabetically,
    MAX(name) AS last_alphabetically 
FROM customers;

-- Find first and last product names
SELECT 
    MIN(product_name) AS first_product,
    MAX(product_name) AS last_product 
FROM products;

-- Find alphabetical range in categories
SELECT 
    MIN(category) AS first_category,
    MAX(category) AS last_category 
FROM products;

🔹 MIN and MAX with WHERE

Combine MIN and MAX with WHERE clauses to find extremes within specific subsets of data. The WHERE clause filters rows first, then MIN and MAX operate only on the filtered results. This lets you find category-specific minimums and maximums or conditional extremes.

-- Find price range for Electronics category
SELECT 
    MIN(price) AS min_price,
    MAX(price) AS max_price 
FROM products 
WHERE category = 'Electronics';

-- Find salary range for Sales department
SELECT 
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary 
FROM employees 
WHERE department = 'Sales';

-- Find date range for completed orders
SELECT 
    MIN(order_date) AS first_completed,
    MAX(order_date) AS last_completed 
FROM orders 
WHERE status = 'Completed';

🔹 MIN and MAX with GROUP BY

Using MIN and MAX with GROUP BY finds minimum and maximum values for each group separately. This powerful combination lets you analyze extremes across different categories, departments, regions, or any grouping. Each group gets its own MIN and MAX calculation independently.

-- Find price range for each category
SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price 
FROM products 
GROUP BY category;

-- Find salary range by department
SELECT 
    department,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary 
FROM employees 
GROUP BY department;

-- Find order date range by customer
SELECT 
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order 
FROM orders 
GROUP BY customer_id;

🔹 Finding Complete Records

To get complete row information for minimum or maximum values, use subqueries or window functions. MIN and MAX alone only return the value, not the entire row. Subqueries let you find which product has the minimum price or which order has the maximum total.

-- Find the cheapest product (complete row)
SELECT * FROM products 
WHERE price = (SELECT MIN(price) FROM products);

-- Find the most expensive product
SELECT * FROM products 
WHERE price = (SELECT MAX(price) FROM products);

-- Find the most recent order
SELECT * FROM orders 
WHERE order_date = (SELECT MAX(order_date) FROM orders);

-- Find oldest employee
SELECT * FROM employees 
WHERE hire_date = (SELECT MIN(hire_date) FROM employees);

🔹 Combining with Other Aggregates

MIN and MAX work well alongside other aggregate functions like COUNT, SUM, and AVG in the same query. This provides comprehensive statistical analysis in a single query, showing you the full picture of your data distribution including extremes, averages, and totals.

-- Complete price statistics
SELECT 
    COUNT(*) AS total_products,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    AVG(price) AS avg_price,
    SUM(price) AS total_value 
FROM products;

-- Order statistics by status
SELECT 
    status,
    COUNT(*) AS order_count,
    MIN(total) AS min_order,
    MAX(total) AS max_order,
    AVG(total) AS avg_order 
FROM orders 
GROUP BY status;