Chapter 23: PostgreSQL MIN and MAX Functions

Part 1: What are MIN and MAX?

MIN returns the minimum (smallest) value in a set of values.
MAX returns the maximum (largest) value in a set of values.

These are aggregate functions – they operate on a set of rows and return a single value.

The Basic Syntax

You can use them together in a single query:

Part 2: Setting Up Our Example Data

Let’s create a comprehensive dataset to explore all the ways MIN and MAX can be used:

---

Part 3: Basic MIN and MAX Usage

1. Finding the Minimum Value

Result:

2. Finding the Maximum Value

Result:

3. Getting Both Extremes Together

Result:

4. MIN and MAX on Different Data Types

With Dates:

Result:

With Text (Alphabetical):

Result:

Notice: Numbers come before letters in ASCII sorting, so “1984” is first.

Part 4: MIN and MAX with WHERE Clauses

1. Filtered Extremes

Result:

2. Multiple Conditions

Result:

3. Date Ranges with MIN/MAX

Part 5: MIN and MAX with GROUP BY

This is where MIN and MAX become truly powerful – finding extremes within each group.

1. Minimum and Maximum per Category

Result:

2. Multiple Aggregates with MIN/MAX

3. Finding Oldest and Newest Products by Category

Part 6: MIN and MAX with HAVING

Filter groups based on their min or max values:

1. Categories with Expensive Products

Result:

2. Categories with Narrow Price Ranges

Result:

3. Categories with Recent Activity

Part 7: MIN and MAX with Joins

Combine MIN/MAX with related tables for deeper insights:

1. Best and Worst Selling Products

For accurate product names, we need subqueries (coming in Part 9).

2. Customer Order Statistics

Result:

3. Product Performance by Category

Part 8: MIN and MAX with NULL Values

NULL handling is crucial with aggregate functions:

1. How NULLs Affect MIN and MAX

Important: MIN and MAX ignore NULL values. They don’t return NULL unless all values are NULL.

2. All NULLs Case

3. Handling NULLs Explicitly

Part 9: Finding the Row with MIN or MAX Value

A common need: find the entire row that contains the minimum or maximum value.

1. Using Subqueries (The Classic Way)

Result:

2. Handling Ties

3. Using ORDER BY with LIMIT (Simpler but Returns Only One)

4. For Each Category – The Most Expensive Product

Better with Window Functions:

Result:

5. Using DISTINCT ON (PostgreSQL-specific)

Part 10: Real-World Examples

Example 1: Sales Dashboard

Example 2: Inventory Management

Example 3: Customer Lifetime Value Analysis

Example 4: Price Range Analysis for Marketing

Example 5: Seasonal Trends

Part 11: Performance Considerations

1. Indexes for MIN and MAX

Indexes dramatically speed up MIN and MAX queries:

2. How PostgreSQL Optimizes MIN/MAX

With an index, PostgreSQL can get the minimum by reading just the first entry in the index:

Look for “Index Only Scan” or “Limit” in the plan – it might show that PostgreSQL only needed to read one index entry!

3. MIN/MAX on Grouped Queries

For grouped queries, a composite index on (category, price) helps:

Part 12: Common Mistakes and How to Avoid Them

Mistake 1: Forgetting NULL Handling

Mistake 2: Using MIN/MAX on Text for Dates

Mistake 3: Assuming MIN/MAX Return the Whole Row

Mistake 4: Not Considering Ties

Mistake 5: Using MIN/MAX on Non-Indexed Columns in Large Tables

Summary: The MIN and MAX Philosophy

MIN and MAX are your window into the boundaries of your data. Master these concepts:

1. Basic usage – Find smallest and largest values in any column

2. With GROUP BY – Find extremes within categories

3. With HAVING – Filter groups based on their extremes

4. With subqueries – Find the actual rows containing extremes

5. Performance – Indexes make MIN/MAX lightning fast

Remember:

• MIN and MAX ignore NULL values

• They work on numbers, dates, text – any orderable data type

• For text, they use alphabetical order

• Combine with GROUP BY for per-category analysis

• Use subqueries or window functions to get full rows

Key applications:

• Price ranges (“What’s our cheapest and most expensive product?”)

• Date ranges (“When was our first and last order?”)

• Performance boundaries (“Who are our highest and lowest spenders?”)

• Quality control (“What are the best and worst rated items?”)

• Inventory management (“Which products have the most and least stock?”)

The MIN and MAX functions transform your database from a collection of data into a source of insights about boundaries, extremes, and ranges. They answer questions like “How bad is the worst case?” and “How good is the best case?” – essential for any data analysis.

Would you like me to elaborate on any specific aspect of MIN and MAX, such as more complex use cases, performance optimization, or integration with other aggregate functions?