MIN and MAX: Finding Extreme Values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Basic minimum and maximum
SELECT 
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;
 
-- Range calculation
SELECT 
    MIN(salary) AS min_sal,
    MAX(salary) AS max_sal,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees;
 
-- Multiple MIN/MAX in one query
SELECT 
    MIN(hire_date) AS earliest_hire,
    MAX(hire_date) AS latest_hire,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
WHERE department = 'Engineering';
 
-- MIN/MAX with expressions
SELECT 
    MIN(quantity * unit_price) AS smallest_order,
    MAX(quantity * unit_price) AS largest_order
FROM order_items;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Sample data with NULLs
-- salaries: 100, NULL, 300, 200, NULL
 
SELECT 
    MIN(salary) AS min_sal,    -- 100 (NULLs ignored)
    MAX(salary) AS max_sal     -- 300 (NULLs ignored)
FROM employees;
 
-- Empty set behavior
SELECT 
    MIN(salary) AS min_sal,    -- NULL
    MAX(salary) AS max_sal     -- NULL
FROM employees
WHERE 1 = 0;  -- No matching rows
 
-- Safe handling with COALESCE
SELECT 
    COALESCE(MIN(salary), 0) AS safe_min,
    COALESCE(MAX(salary), 0) AS safe_max
FROM employees
WHERE department = 'NonExistent';
 
-- Checking for complete NULL data
SELECT 
    CASE 
        WHEN MIN(salary) IS NULL THEN 'No salary data'
        ELSE 'Salary data exists'
    END AS data_status
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13

-- Numeric MIN/MAX
SELECT 
    MIN(temperature) AS coldest,   -- Handles negatives correctly
    MAX(temperature) AS hottest
FROM weather_data;
-- If data is -10, 5, 15: MIN = -10, MAX = 15
 
-- Decimal precision
SELECT 
    MIN(price) AS lowest_price,    -- Preserves decimal precision
    MAX(price) AS highest_price
FROM products;
-- If data is 9.99, 19.95, 29.99: MIN = 9.99, MAX = 29.99

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- String MIN/MAX follows collation order
SELECT 
    MIN(name) AS first_alphabetically,
    MAX(name) AS last_alphabetically
FROM employees;
-- Depends on collation: case-sensitive vs case-insensitive
 
-- Case sensitivity example (ASCII/binary collation)
-- Values: 'Apple', 'banana', 'CHERRY'
-- MIN = 'Apple' (A=65 < C=67 < b=98 in ASCII)
-- MAX = 'banana' (b=98 > all uppercase letters in ASCII)
-- This may surprise you!
 
-- Case-insensitive collation gives expected alphabetical order
SELECT 
    MIN(name COLLATE SQL_Latin1_General_CP1_CI_AS) AS first_name,
    MAX(name COLLATE SQL_Latin1_General_CP1_CI_AS) AS last_name
FROM products;  -- SQL Server syntax
 
-- PostgreSQL: specify collation
SELECT 
    MIN(name COLLATE "en_US.utf8") AS first_name
FROM products;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Date/Time types (chronological ordering)
SELECT 
    MIN(order_date) AS first_order,
    MAX(order_date) AS most_recent_order
FROM orders;
 
-- Timestamp precision
SELECT 
    MIN(created_at) AS earliest_timestamp,
    MAX(updated_at) AS latest_activity
FROM audit_log;
 
-- Date range analysis
SELECT 
    MIN(birth_date) AS oldest_employee,
    MAX(birth_date) AS youngest_employee,
    AGE(MIN(birth_date)) AS oldest_age,
    AGE(MAX(birth_date)) AS youngest_age  -- PostgreSQL AGE function
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- Boolean MIN/MAX (in databases that support it)
SELECT 
    MIN(is_active) AS any_inactive,  -- FALSE if any row is FALSE
    MAX(is_active) AS any_active     -- TRUE if any row is TRUE
FROM accounts;
 
-- More common: use BOOL_AND / BOOL_OR (PostgreSQL)
SELECT 
    BOOL_AND(is_verified) AS all_verified,  -- TRUE only if ALL are TRUE
    BOOL_OR(is_suspended) AS any_suspended  -- TRUE if ANY is TRUE
FROM users;
 
-- SQL Server equivalent using MIN/MAX
SELECT 
    CASE MIN(CAST(is_active AS INT)) WHEN 0 THEN 'Some Inactive' ELSE 'All Active' END,
    CASE MAX(CAST(is_premium AS INT)) WHEN 1 THEN 'Has Premium' ELSE 'No Premium' END
FROM customers;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Fast: Uses index efficiently
-- Assuming index on 'created_at'
SELECT MAX(created_at) FROM orders;
-- Execution: Index scan to last entry, O(1) or O(log n)
 
-- Also fast: Simple WHERE with indexed column
SELECT MIN(order_date) FROM orders WHERE customer_id = 12345;
-- If (customer_id, order_date) index exists, very efficient
 
-- Slower: Complex WHERE may prevent optimization
SELECT MIN(price) FROM products WHERE LOWER(name) LIKE '%widget%';
-- LIKE with leading wildcard prevents index use on 'name'
-- Function on column (LOWER) may prevent index use
 
-- Slower: GROUP BY requires finding MIN/MAX per group
SELECT customer_id, MIN(order_date), MAX(order_date)
FROM orders
GROUP BY customer_id;
-- Must process each group, though indexes still help
 
-- Check execution plan to verify optimization
EXPLAIN ANALYZE SELECT MAX(created_at) FROM orders;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Optimization: Combined MIN/MAX in single scan
SELECT MIN(price), MAX(price) FROM products;
-- Database processes table once, tracking both
 
-- Anti-pattern: Separate queries
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;
-- Two separate scans (unless cached)
 
-- Optimization: Correlated scalar subquery for "row with MAX"
-- Find the employee with highest salary
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
 
-- Or use window function (often more efficient)
SELECT *
FROM (
    SELECT *, MAX(salary) OVER () AS max_sal
    FROM employees
) sub
WHERE salary = max_sal;
 
-- Or ORDER BY with LIMIT (very efficient with index)
SELECT * FROM employees ORDER BY salary DESC LIMIT 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Salary range by department
SELECT 
    department_id,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(salary) - MIN(salary) AS salary_spread
FROM employees
GROUP BY department_id
ORDER BY salary_spread DESC;
 
-- Date range by customer (account lifespan)
SELECT 
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    MAX(order_date) - MIN(order_date) AS days_between_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;  -- Only customers with multiple orders
 
-- Price extremes by category and year
SELECT 
    category,
    EXTRACT(YEAR FROM sale_date) AS year,
    MIN(unit_price) AS lowest_price,
    MAX(unit_price) AS highest_price,
    AVG(unit_price) AS avg_price
FROM sales
GROUP BY category, EXTRACT(YEAR FROM sale_date)
ORDER BY category, year;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Goal: Find the full record of highest-paid employee per department
 
-- Approach 1: Correlated subquery (portable but can be slow)
SELECT *
FROM employees e1
WHERE salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);
 
-- Approach 2: Join with aggregated subquery
SELECT e.*
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) m ON e.department_id = m.department_id AND e.salary = m.max_salary;
 
-- Approach 3: Window function (usually most efficient)
SELECT *
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;
 
-- Approach 4: DISTINCT ON (PostgreSQL specific)
SELECT DISTINCT ON (department_id) *
FROM employees
ORDER BY department_id, salary DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Pattern: Range validation
-- Check if values fall within expected bounds
SELECT 
    CASE 
        WHEN MIN(quantity) < 0 THEN 'Invalid: Negative quantities found'
        WHEN MAX(quantity) > 10000 THEN 'Warning: Unusually large quantities'
        ELSE 'All quantities within expected range'
    END AS validation_result
FROM order_items;
 
-- Pattern: Gap detection
-- Find missing numbers in a sequence
SELECT 
    MIN(id) AS first_id,
    MAX(id) AS last_id,
    COUNT(*) AS actual_count,
    MAX(id) - MIN(id) + 1 AS expected_count,
    (MAX(id) - MIN(id) + 1) - COUNT(*) AS gap_count
FROM invoice_ids;
 
-- Pattern: Freshness check
-- How recent is our data?
SELECT 
    MAX(updated_at) AS last_update,
    CURRENT_TIMESTAMP - MAX(updated_at) AS time_since_update,
    CASE 
        WHEN MAX(updated_at) < CURRENT_TIMESTAMP - INTERVAL '1 hour' THEN 'STALE'
        ELSE 'FRESH'
    END AS data_status
FROM sensor_readings;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Pattern: Generating sequences with bounds
-- Create a date series covering all order dates
SELECT 
    generate_series(
        (SELECT MIN(order_date) FROM orders)::date,
        (SELECT MAX(order_date) FROM orders)::date,
        '1 day'::interval
    ) AS date_in_range;
 
-- Pattern: Conditional MIN/MAX
SELECT 
    MIN(CASE WHEN status = 'completed' THEN order_date END) AS first_completed,
    MAX(CASE WHEN status = 'completed' THEN order_date END) AS last_completed,
    MIN(CASE WHEN status = 'pending' THEN order_date END) AS oldest_pending
FROM orders;
 
-- Pattern: Running MIN/MAX (window functions)
SELECT 
    date,
    price,
    MIN(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_min,
    MAX(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max
FROM stock_prices;
 
-- Pattern: High-water mark (rolling maximum)
SELECT 
    date,
    revenue,
    MAX(revenue) OVER (ORDER BY date) AS revenue_high_water_mark,
    revenue - MAX(revenue) OVER (ORDER BY date) AS drawdown_from_peak
FROM daily_revenue;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Get the first and last value when ordered by another column
-- Goal: For each customer, get first purchase amount and last purchase amount
 
-- Using window functions
SELECT DISTINCT
    customer_id,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id ORDER BY order_date
    ) AS first_purchase,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_purchase
FROM orders;
 
-- Alternative: Subqueries with MIN/MAX date
SELECT 
    customer_id,
    (SELECT amount FROM orders o2 
     WHERE o2.customer_id = o.customer_id 
     ORDER BY order_date LIMIT 1) AS first_purchase,
    (SELECT amount FROM orders o2 
     WHERE o2.customer_id = o.customer_id 
     ORDER BY order_date DESC LIMIT 1) AS last_purchase
FROM (SELECT DISTINCT customer_id FROM orders) o;
 
-- PostgreSQL: ARRAY_AGG with subscript
SELECT 
    customer_id,
    (ARRAY_AGG(amount ORDER BY order_date))[1] AS first_purchase,
    (ARRAY_AGG(amount ORDER BY order_date DESC))[1] AS last_purchase
FROM orders
GROUP BY customer_id;