How to use SQL `MIN` and `MAX`

In SQL, MIN and MAX are aggregate functions that are used to find the smallest and largest values in tables and columns. They optimize and accelerate data queries and facilitate efficient data evaluation for determining trends, anomalies and processes.

What are SQL MIN and MAX?

SQL MIN and MAX are aggregate functions that perform calculations with numerical values and return a single value. You can use MIN and MAX in datasets, tables, columns or rows to find and compare the smallest and largest values.

MIN and MAX significantly speed up complex data evaluations, making them essential functions in SQL. They allow you to find anomalies or to quickly filter product, price and cost tables for minimum and maximum values. Depending on which optional parameters you use, you can also combine the functions with SQL commands like WHERE, JOIN, HAVING and GROUP BY.

What is the syntax of SQL MIN and MAX?

SQL MIN and `MAX can be used alone for simple data queries or in combination with other SQL operators for more complex analyses.

The syntax looks like this:

MIN(column or string)
MAX(column or string)

If you want to apply the function to a subset of numerical values in your dataset, combine MIN and MAX with the SQL command SELECT and a FROM clause. You can use them to select a table and a column or row like this:

SELECT  MIN(column or string) FROM  table
MAX(column or string)  FROM  tablename

You can also use these additional parameters and commands to make your query more precise:

• DISTINCT: Eliminates duplicates and duplicate rows in data queries to get unique lists
• WHERE: Excludes certain records before applying MIN and MAX and concentrates only on the subsets you define
• GROUP BY: Groups queried records into one or more columns based on, e.g., region or category, making it possible to analyze trends and patterns
• HAVING: Filters results after the aggregation of min and max values, displaying results that meet certain criteria
• BETWEEN: In combination with MIN and MAX, limits results to a certain range of values
• JOIN: Allows you to combine min and max values from multiple tables into a single table

What are SQL MIN and MAX used for?

There are countless uses for SQL MIN and MAX, especially when they’re combined with other parameters. Some possible use cases included:

• Filtering highest and lowest prices in product tables
• Returning largest and smallest files
• Salary analyses for employees
• Showing the frequency of strings, words or values in a dataset
• Detecting anomalies that exceed thresholds or average values
• Recognizing performance peaks and weaknesses
• Analyzing inventories and products
• Narrowing down data analyses for large datasets

Examples of SQL MIN and MAX

We’ll now turn to some practical examples that show how SQL MIN and MAX are applied. In our examples, we’ll look at a table with products and their prices. Note that we’ll use AS in the syntax to name the column for MIN and MAX in the target table.

Our example table:

Now we’ll get the products with the highest and lowest price from the table and return them as single lines:

MIN:

SELECT  MIN(price)  FROM  products;

MAX:

SELECT  MAX(price)  FROM  products;

If you want to return the two values in a table, use the following syntax and define the column with the min and max values using “AS”.

SELECT  MIN(price)  AS  min_price,  MAX(price)  AS  max_price  FROM  products

You can make your search even more precise using additional commands and parameters. If you want to display the minimum and maximum values from each product category, you can use the GROUP BY command:

SELECT  category,
MIN(price)  AS min_price,  MAX(price)  AS  max_price
FROM  products
GROUP BY  category

In the target table, you’ll get the lowest and highest values grouped according to the category of the product.

HAVING allows you to additionally filter values that fulfill certain criteria. You can specify, for example, that the minimum value of a product shouldn’t be included if it falls below a certain threshold. The syntax for that would look as follows:

SELECT  category,
MAX(price)  AS  max_price
FROM  products
GROUP BY  category
HAVING MIN(price)>10;

When this code is used on our example table above, the body lotion that costs $9.99 would not be included in the target table.

What are some alternatives to SQL MIN and MAX?

SQL MIN and MAX is one of SQL’s aggregate functions. Aside from those two functions, there are number of other similar functions that can be used for returning aggregate values:

• AVG: SQL AVG returns the average value of a table or column – for example, the average temperature of months in a year
• COUNT: SQL COUNT counts the number of records in a table or column
• SUM: SQL SUM returns the sum of all the values in a column
• BETWEEN: SQL BETWEEN analyzes the data in a value range
• LIKE: The operator SQL LIKE returns numerical values or strings that match a search pattern