SQL MIN() and MAX()

MIN() and MAX() Functions in SQL

The MIN() function in SQL returns the smallest value in a selected column, while the MAX() function returns the largest value in a selected column. These functions are commonly used to analyze numerical or date data, but they can also be applied to text columns to find the lexicographically smallest or largest values.

Why Use MIN() and MAX()?

1. Analyze Extremes: Identify the minimum or maximum value in a dataset, such as the lowest price or the highest salary.
2. Data Insights: Quickly understand the range of data within a column.
3. Support Decision-Making: Determine top performers, best-selling products, or minimum requirements.

Syntax

For both functions, the syntax is similar:

1
2
3
4
5
6
7
SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Key Components:

• MIN(column_name): Finds the smallest value in the column.
• MAX(column_name): Finds the largest value in the column.
• WHERE condition: (Optional) Filters rows to include only those that meet a condition.

Examples of MIN() and MAX() Functions

Find the Lowest Test Score

Suppose you have a TestScores table. To find the lowest score:

1
2
SELECT MIN(Score) AS LowestScore
FROM TestScores;

Explanation: This query retrieves the smallest value from the Score column and renames the result column as LowestScore using the AS keyword.

Find the Highest Test Score

To find the highest score in the same table:

1
2
SELECT MAX(Score) AS HighestScore
FROM TestScores;

Explanation: This query retrieves the largest value from the Score column and renames the result column as HighestScore.

Find the Earliest and Latest Event Dates

In an Events table, to find the earliest and latest event dates:

Earliest event date:

1
2
SELECT MIN(EventDate) AS EarliestDate
FROM Events;

Latest event date:

1
2
SELECT MAX(EventDate) AS LatestDate
FROM Events;

Explanation: These queries identify the smallest and largest values in the EventDate column, showing the earliest and latest events.

Use MIN() and MAX() with a WHERE Clause

To find the lowest price of products in the "Electronics" category:

1
2
3
SELECT MIN(Price) AS LowestPrice
FROM Products
WHERE Category = 'Electronics';

To find the highest price of products priced above 50:

1
2
3
SELECT MAX(Price) AS HighestPrice
FROM Products
WHERE Price > 50;

Explanation: These queries filter the dataset using a WHERE clause before applying the MIN() or MAX() function.

Use MIN() and MAX() with GROUP BY

The GROUP BY clause allows you to apply MIN() and MAX() to groups of data. For example, find the lowest and highest price in each product category:

Find the lowest price in each category:

1
2
3
SELECT Category, MIN(Price) AS LowestPrice
FROM Products
GROUP BY Category;

Find the highest price in each category:

1
2
3
SELECT Category, MAX(Price) AS HighestPrice
FROM Products
GROUP BY Category;

Explanation: These queries group the rows by Category and then calculate the lowest and highest prices for each group.

Best Practices for MIN() and MAX()

• Use Descriptive Aliases: Use the AS keyword to provide meaningful names for the output columns.
• Combine with Filters: Use the WHERE clause to narrow down the dataset before applying MIN() or MAX().
• Group Data When Needed: Use GROUP BY to calculate minimum and maximum values for subsets of data.
• Test Queries: Ensure your query returns the correct results by validating against sample data.

Key Takeaways

• Purpose: The MIN() function retrieves the smallest value, while the MAX() function retrieves the largest value in a column.
• Common Usage: These functions are widely used for analyzing ranges in numerical, date, and text data.
• Grouping: Combine with GROUP BY to calculate values for specific groups.
• Filtering: Use WHERE to filter the dataset before applying the functions.
• Versatility: Works with numerical, date, and text columns.