MIN Function

-- Smallest salary in the company
SELECT MIN(salary) AS lowest_salary
FROM employees;

-- Earliest hire date
SELECT MIN(hire_date) AS first_hire
FROM employees;

-- Alphabetically first name
SELECT MIN(first_name) AS first_name_alphabetically
FROM employees;

MAX Function

-- Highest salary in the company
SELECT MAX(salary) AS highest_salary
FROM employees;

-- Most recent hire date
SELECT MAX(hire_date) AS latest_hire
FROM employees;

-- Last name alphabetically
SELECT MAX(last_name) AS last_name_alphabetically
FROM employees;

MIN and MAX with GROUP BY

-- Salary range per department
SELECT department,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary,
       MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY department
ORDER BY salary_range DESC;

-- Earliest and latest hire dates per department
SELECT department,
       MIN(hire_date) AS first_hire,
       MAX(hire_date) AS last_hire
FROM employees
GROUP BY department;

Finding the Row with MAX Value

-- Get the employee with the highest salary (single row)
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Get top N highest salaries
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Get employees with maximum salary per department
SELECT e.*
FROM employees e
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department = e.department
);

MIN and MAX with Dates

-- Date range analysis
SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       DATEDIFF(DAY, MIN(order_date), MAX(order_date)) AS days_span
FROM orders;

-- Orders per month with date boundaries
SELECT YEAR(order_date) AS order_year,
       MONTH(order_date) AS order_month,
       MIN(order_date) AS month_start,
       MAX(order_date) AS month_end,
       COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;

MIN and MAX with Strings

-- String ordering uses lexicographic comparison
SELECT MIN(product_name) AS first_product,
       MAX(product_name) AS last_product
FROM products;

-- Find products alphabetically within each category
SELECT category,
       MIN(product_name) AS first_product,
       MAX(product_name) AS last_product
FROM products
GROUP BY category;

MIN and MAX with CASE

-- Conditional MIN/MAX
SELECT department,
       MIN(CASE WHEN gender = 'M' THEN salary END) AS min_male_salary,
       MAX(CASE WHEN gender = 'M' THEN salary END) AS max_male_salary,
       MIN(CASE WHEN gender = 'F' THEN salary END) AS min_female_salary,
       MAX(CASE WHEN gender = 'F' THEN salary END) AS max_female_salary
FROM employees
GROUP BY department;

Window Functions with MIN/MAX

-- Compare each employee's salary to department min/max
SELECT employee_name, department, salary,
       MIN(salary) OVER (PARTITION BY department) AS dept_min,
       MAX(salary) OVER (PARTITION BY department) AS dept_max,
       ROUND((salary - MIN(salary) OVER (PARTITION BY department)) * 100.0 /
             NULLIF(MAX(salary) OVER (PARTITION BY department) - MIN(salary) OVER (PARTITION BY department), 0), 1) AS pct_in_range
FROM employees;

-- Running min and max
SELECT order_date, amount,
       MIN(amount) OVER (ORDER BY order_date) AS running_min,
       MAX(amount) OVER (ORDER BY order_date) AS running_max
FROM orders;

Common MIN/MAX Patterns

-- Find duplicate values (min = max in a group)
SELECT department, job_title, COUNT(*) AS cnt
FROM employees
GROUP BY department, job_title
HAVING MIN(salary) = MAX(salary);

-- Detect outliers (values outside 2x the average)
SELECT *
FROM employees
WHERE salary > 2 * (SELECT AVG(salary) FROM employees);

-- Find the most recent record per group
SELECT e.*
FROM employees e
WHERE hire_date = (
    SELECT MAX(hire_date)
    FROM employees
    WHERE department = e.department
);

NULL Handling

-- MIN and MAX ignore NULLs
SELECT MIN(salary) AS min_salary,  -- Ignores NULLs
       MAX(salary) AS max_salary   -- Ignores NULLs
FROM employees;

-- If all values are NULL, result is NULL
SELECT MIN(NULL) AS result;  -- Returns NULL

Performance Tips