SQL — Min and Max

MIN() and MAX()

MIN() returns the smallest value; MAX() returns the largest. They work on numbers, dates, and strings.

Basic Examples

SELECT MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary
FROM employees;

MIN/MAX with WHERE

-- Highest salary in Engineering
SELECT MAX(salary) AS top_salary
FROM employees
WHERE department = 'Engineering';

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

MIN/MAX with GROUP BY

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

MIN/MAX with JOIN

SELECT
  c.category_name,
  MIN(p.price) AS cheapest,
  MAX(p.price) AS most_expensive
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.category_name;

Finding the Row with MAX Value

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

-- Or using ORDER BY + LIMIT
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 1;

MIN/MAX on Dates

SELECT
  MIN(order_date) AS first_order,
  MAX(order_date) AS last_order,
  DATEDIFF(MAX(order_date), MIN(order_date)) AS days_between
FROM orders;

MIN/MAX on Strings

Alphabetical comparison:

SELECT
  MIN(name) AS first_alphabetically,
  MAX(name) AS last_alphabetically
FROM employees;

MIN/MAX with NULL

NULL values are ignored:

-- Returns the lowest non-NULL salary
SELECT MIN(salary) FROM employees;

MIN/MAX vs ORDER BY

-- Get the single highest value
SELECT MAX(salary) FROM employees;

-- Get the top 5 highest values
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Practical Example

-- Price range per category
SELECT
  c.category_name,
  COUNT(p.id) AS product_count,
  ROUND(MIN(p.price), 2) AS min_price,
  ROUND(AVG(p.price), 2) AS avg_price,
  ROUND(MAX(p.price), 2) AS max_price
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.category_name
HAVING COUNT(p.id) > 5
ORDER BY max_price DESC;

Practice

1. Find the cheapest and most expensive product in each category
2. Find the date range of orders (first and last order)
3. Find the employee with the longest name
4. Calculate the price range for each product category