Finding Minimum and Maximum Values with MIN/MAX

Understanding the range of values within your data is often very informative. You might need to find the lowest price, the highest sales figure, the earliest date, or the latest activity. SQL provides two simple yet effective aggregate functions for this purpose: MIN() and MAX(). These functions help identify the boundary values in a column, either across an entire table or within specific groups.

Finding the Minimum Value with MIN()

The MIN() function scans a specified column and returns its smallest value. This is useful for identifying starting points, lowest costs, earliest times, or minimum quantities.

Its basic syntax is straightforward:

SELECT MIN(column_name)
FROM table_name;

Imagine you have a Products table containing information about items sold, including their prices. To find the price of the cheapest product available, you would use MIN() on the price column:

SELECT MIN(price) AS lowest_price
FROM Products;

This query looks at all the values in the price column and returns only the single smallest value found. We use AS lowest_price to give the result column a descriptive name.

MIN() also works effectively with the GROUP BY clause. If you wanted to find the lowest price for a product within each category, you could group the products by their category and then apply MIN():

SELECT
  category,
  MIN(price) AS lowest_price_in_category
FROM Products
GROUP BY category;

This query first groups rows based on the category column. Then, for each group (each distinct category), it finds the minimum value in the price column and returns the category name alongside its corresponding minimum price.

Finding the Maximum Value with MAX()

Complementary to MIN(), the MAX() function finds the largest value in a specified column. This helps identify peaks, highest costs, latest times, or maximum quantities.

The syntax mirrors that of MIN():

SELECT MAX(column_name)
FROM table_name;

Using our Products table again, if you wanted to find the most expensive product, you would use MAX() on the price column:

SELECT MAX(price) AS highest_price
FROM Products;

This query returns the single largest value from the price column across all products.

Like MIN(), MAX() is often used with GROUP BY. To find the highest price within each product category:

SELECT
  category,
  MAX(price) AS highest_price_in_category
FROM Products
GROUP BY category;

Here, the query groups products by category and then identifies the maximum price for each unique category.

Using MIN() and MAX() Together

You can use MIN() and MAX() in the same query to quickly understand the range of values for a particular attribute. For instance, to find the earliest and latest order dates in an Orders table:

SELECT
  MIN(order_date) AS earliest_order_date,
  MAX(order_date) AS latest_order_date
FROM Orders;

This provides a concise summary of the time span covered by the orders data.

Combining this with GROUP BY, you could find the lowest and highest order total for each customer:

SELECT
  customer_id,
  MIN(order_total) AS min_order_total,
  MAX(order_total) AS max_order_total
FROM Orders
GROUP BY customer_id;

This query gives you insight into the spending range for each individual customer.

Data Types and NULL Handling

MIN() and MAX() work as expected with numeric types (like INTEGER, DECIMAL, FLOAT) and date/time types (like DATE, TIMESTAMP). They can also operate on character strings (like VARCHAR, TEXT), where they typically return the "first" or "last" value based on alphabetical sorting (e.g., MIN() returns 'Apple', MAX() returns 'Zebra').

It's important to know that MIN() and MAX(), like most aggregate functions, ignore NULL values when performing their calculations. If a column contains only NULL values, both MIN() and MAX() will return NULL.

By using MIN() and MAX(), you gain valuable tools for quickly assessing the boundaries and ranges within your datasets, which is a fundamental step in data exploration and analysis.