SQL Min and Max Functions
This article will explore what each function does, its corresponding syntax, and practical examples of when to use these functions.
NOTE: This tutorial will illustrate how to use these functions in ANSI SQL, also known as Standard SQL. However, they should perform similar actions even in other SQL flavors.”
SQL Min() Function
Let us start with the min() function. This function allows you to fetch the smallest value in a set of given values. You can provide the input values as a numerical column or a result of an expression that returns a numerical value.
The min() function is an aggregate function and returns a single numeric value from a given input.
The function syntax can be expressed as shown:
The function accepts a single argument as an expression or a given column set.
Take the example below:
Running the code above should return the smallest value from the given array.
SQL Max() Functions
As the name suggests, the max() function allows you to get the largest number in a given set of numerical values. Similarly, you can pass the set of values as a numerical column or a result of a given expression that returns a numerical value.
The function syntax is as shown:
Consider the example below:
The code above should fetch the largest value from the given array. An example output is as shown:
Let us use accurate world data best to illustrate various uses of the min and max functions. Take the example data shown below:
NOTE: The data above is purely fictional and does not represent actual database capabilities or compression ratio.
Example 1 – Using Min and Max Functions on Single Column
We can use the min and max functions on a given table column to fetch the smallest and largest value from the column, respectively.
For example, to get the database with the smallest size on disk, we can run the query as:
The query above should return the smallest size from the size_on_disk column, as shown in the output below:
For the largest size, use the max function:
Example 2 – Using Min and Max Functions With GROUP BY
We can also include other columns using the group by function when working with an aggregate function.
For example, to show the server_name:
The group by clause is instrumental when you categorize data into specific partitions and find a minimum or maximum value from each division.
Example 3 – Using Min and Max in the Same Query
We can also use two aggregate functions in the same query, as shown in the output below:
An example output is as shown:
Example 4 – Combining Min and Max Functions With the Having Clause
The example below shows how to use the min and max() functions to filter results using the having clause.
In this post, we extensively explored how to use the min and max() functions in SQL. We also discussed practical examples of using these two functions in your applications.
Thanks for reading!!