PostgreSQL: MIN and MAX Functions – Finding Minimum and Maximum Values

Welcome to The Coding College, where we simplify coding concepts for learners! In this tutorial, we’ll explore PostgreSQL’s MIN and MAX functions, which are essential for finding the smallest and largest values in a dataset.

What are MIN and MAX Functions?

• MIN: Returns the smallest value in a specified column.
• MAX: Returns the largest value in a specified column.

These functions work on numeric, date, and even text data types in PostgreSQL.

Syntax

SELECT MIN(column_name) AS minimum_value, MAX(column_name) AS maximum_value
FROM table_name
[WHERE condition];

• column_name: The column you want to evaluate.
• AS: Provides an alias for the returned value.
• WHERE: (Optional) Filters rows before applying the function.

Example: Sample Table

Consider a table named products:

Using MIN and MAX

Example 1: Find the Minimum and Maximum Prices

SELECT MIN(price) AS minimum_price, MAX(price) AS maximum_price
FROM products;

Result:

Using MIN and MAX with WHERE Clause

Example 2: Find the Price Range for Electronics

SELECT MIN(price) AS minimum_price, MAX(price) AS maximum_price
FROM products
WHERE category = 'Electronics';

Result:

Combining with Other Columns

Example 3: Find the Product with the Maximum Price

SELECT product_name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Result:

Example 4: Find the Product with the Minimum Price

SELECT product_name, price
FROM products
WHERE price = (SELECT MIN(price) FROM products);

Result:

Aggregating with Groups

You can use MIN and MAX with the GROUP BY clause to find the minimum and maximum values for each group.

Example 5: Find the Minimum and Maximum Price by Category

SELECT category, MIN(price) AS minimum_price, MAX(price) AS maximum_price
FROM products
GROUP BY category;

Result:

Real-World Applications

1. Price Analysis: Identify the cheapest and most expensive items in your store.
2. Performance Metrics: Find the best and worst performers in an organization.
3. Date Ranges: Determine the earliest and latest dates in a dataset.

Learn More at The Coding College

At The Coding College, we focus on user-friendly tutorials designed to enhance your learning experience. We adhere to Google’s E-E-A-T principles, ensuring expert and accurate content for coding enthusiasts.

Conclusion

PostgreSQL’s MIN and MAX functions are simple yet powerful tools for data analysis. Whether you’re working with prices, dates, or any other values, these functions help you extract key insights quickly and efficiently.

Stay tuned to The Coding College for more PostgreSQL tutorials and other programming tips!