PostgreSQL MIN and MAX

PostgreSQL MIN() and MAX()

The MIN() and MAX() functions are built-in aggregate functions in PostgreSQL. They are specifically designed to quickly find extreme values in a dataset.

Instead of returning raw rows of data, they return a single, computed value. They scan the entire targeted column to extract the absolute lowest or highest entry.

The MIN() Function

The MIN() function returns the smallest numeric value found in a selected column. If applied to text, it returns the first alphabetical value (like 'A').

If applied to dates, it will cleanly return the oldest chronological date. It completely ignores all NULL values during its evaluation process.

-- Find the absolutely cheapest product price
SELECT MIN(Price) AS SmallestPrice
FROM Products;

The MAX() Function

The MAX() function acts as the exact opposite of the minimum function. It swiftly returns the largest numerical value present in a selected column.

If applied to strings, it returns the last alphabetical value (like 'Z'). If applied to dates, it will accurately return the most recent date possible.

-- Find the most expensive product price
SELECT MAX(Price) AS LargestPrice
FROM Products;

Using Aliases

When you use an aggregate function, the resulting column name can look messy. By default, PostgreSQL might name the output column simply "min" or "max".

To make the output readable, we heavily utilize the AS keyword. This temporarily renames the output column to a friendly, descriptive alias.

Filtering Aggregates

You do not have to scan the entire table to find a minimum or maximum. You can seamlessly combine these functions with a standard WHERE clause.

This allows you to find the most expensive product within a specific category. The database filters the rows first, and then calculates the extreme value.

-- Find the maximum price, but only for products under ID 4
SELECT MAX(Price) AS HighestLocalPrice
FROM Products
WHERE ProductID < 4;

Summary

MIN() and MAX() are incredibly fast, optimized analytical tools. They effortlessly handle numbers, strings, and complex timestamp dates natively.

Always use the AS keyword to give your calculated outputs a readable name.