PostgreSQL Min and Max

MIN() and MAX() find the smallest and largest values in a column, working with numbers, text, and dates alike.

The MIN() and MAX() functions

MIN() returns the lowest value in a set of rows, and MAX() returns the highest. Both functions ignore NULL values automatically, so you never have to filter them out yourself.

Unlike SUM() or AVG(), which only make sense for numbers, MIN() and MAX() work on almost any comparable data type: integers, decimals, text strings, dates, and timestamps. For text, PostgreSQL compares values alphabetically; for dates, it compares chronologically.

Cheapest and most expensive product

SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM products;

Using MIN and MAX with dates

A very common use case is finding the earliest or most recent event in a table, such as the first order a customer placed or the most recent login.

Earliest and latest order dates

SELECT MIN(order_date) AS first_order, MAX(order_date) AS latest_order
FROM orders;

MIN and MAX per group

Just like other aggregates, MIN() and MAX() are frequently combined with GROUP BY to answer questions such as 'what is the highest paid employee in each department?'

  • MIN(column) with GROUP BY returns the smallest value per group
  • MAX(column) with GROUP BY returns the largest value per group
  • You can call MIN() and MAX() on the same column in one query
  • Combine with ORDER BY to sort the summarized results

Highest salary per department

SELECT department_id, MAX(salary) AS top_salary
FROM employees
GROUP BY department_id
ORDER BY top_salary DESC;
ExpressionResult meaning
MIN(price)Lowest price across matching rows
MAX(hire_date)Most recent hire date
MIN(name)Alphabetically first name
Note: MIN() and MAX() can use an index on the target column to return the result almost instantly, without scanning the whole table, when there is no GROUP BY clause.
Note: If the table or matching group has zero rows, MIN() and MAX() return NULL rather than raising an error, so check for NULL before using the result in further calculations.

Keep Going

PostgreSQL Interview QuestionsPractice real questions asked for PostgreSQLDatabase Administrator SalarySee what Database Administrator roles pay in the marketAuto Apply for JobsApply to open roles related to PostgreSQL and tweak your resume