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 optimise and ac­cel­er­ate data queries and fa­cil­it­ate efficient data eval­u­ation for de­term­in­ing trends, anomalies and processes.

What are SQL MIN and MAX?

SQL MIN and MAX are aggregate functions that perform cal­cu­la­tions 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 sig­ni­fic­antly speed up complex data eval­u­ations, 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 para­met­ers 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 com­bin­a­tion 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 ad­di­tion­al para­met­ers and commands to make your query more precise:

• DISTINCT: Elim­in­ates du­plic­ates and duplicate rows in data queries to get unique lists
• WHERE: Excludes certain records before applying MIN and MAX and con­cen­trates 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 analyse trends and patterns
• HAVING: Filters results after the ag­greg­a­tion of min and max values, dis­play­ing results that meet certain criteria
• BETWEEN: In com­bin­a­tion 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, es­pe­cially when they’re combined with other para­met­ers. 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
• Re­cog­nising per­form­ance peaks and weak­nesses
• Analysing in­vent­or­ies 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 ad­di­tion­al commands and para­met­ers. 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 ad­di­tion­ally 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 al­tern­at­ives 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 tem­per­at­ure 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 analyses the data in a value range
• LIKE: The operator SQL LIKE returns numerical values or strings that match a search pattern