SQL Min and Max

Introduction to SQL Min and Max

SQL min and max functions are used to find the smallest and largest value from the specified table column. The SQL min function returns the smallest value from the specified column. At the same time, the max function returns the most significant value from the specified column. It is an aggregation function used in all the RDBMS database systems to find the smallest and largest values from table columns.

What are SQL Min and Max?

SQL min and max aggregation functions will work on the numerical data. The SQL min and max function takes the single argument as input, an expression, or a column. After taking a single input, both functions return a single value as output.

SQL Min

The SQL min function returns a single value as output as we defined. This function will return the smallest values from a set of elements. SQL min function is straightforward to use. It will be recording the minimum number from the record set. The SQL min function is essential and helpful in finding the smallest number from the table column.

Below is the syntax SQL min function:

Select MIN (name_of_column) from table_name;

Select MIN (name_of_column) from table_name where condition;

MIN (name_of_column or expression)

Select MIN (name_of_column) from table_name group by name_of_column;

SQL Max

As we defined SQL max function is returning a single value as output. This function will return the most significant values from a set of elements. SQL max function is straightforward to use. It will be recording the maximum number from the record set. The SQL max function is essential and helpful in finding the most significant number from the table column.

Below is the syntax SQL max function:

Select MAX (name_of_column) from table_name;

Select MAX (name_of_column) from table_name where condition;

Select MAX (name_of_column) from table_name group by name_of_column;

MAX (name_of_column or expression)

Parameter description

• Select: We can select the column name from the table to find the min and max values from the table column. This is the SQL statement that selects the specified data from a table.
• Name of the table: This is nothing but the table name from which we have retrieved the column smallest and largest values. When retrieving the smallest or largest values using it, we need to define the table name.
• Where condition: This is the most critical parameter executing the SQL min and max function. The where condition in sql min and max function will be used to retrieve specified most minor and most significant, which we have defined in the where condition. Where condition is beneficial to retrieve specific condition smallest and largest values using sql min and max functions.
• Name of the column: This is the table column used with functions. If suppose we have used sql min and max function with the specified column, it will be retrieving the smallest and largest values from the specified column.
• Min: This function finds the smallest values from the column or expression we used in our query.
• Max: This function is used to find the most significant values from the column or expression we used in our query.

SQL Min and Max Functions

We can also use group by statement with SQL min and max function and the order by condition with min and max functions.

The below example shows that sql min function is not case sensitive. In the first example, we have used the function name in an uppercase letter. In contrast, in the second example, we have used the function name in lowercase letters both times, and it will return the same result without issuing any error. So we can say that sql min function is not case sensitive.

Code:

SELECT MIN (id) from min_max;
SELECT min (id) from min_max;

Output:

The below example shows that sql max function is not case sensitive. In the first example, we have used the function name in an uppercase letter. In contrast, in the second example, we have used the function name in lowercase letters both times, which will return the same result without issuing any error. So we can say that sql max function is not case sensitive.

Code:

SELECT MAX (id) from min_max;
SELECT max (id) from min_max;

Output:

The function is not working on text types of columns. We need only the numeric type of column to find the smallest and largest values.

Code:

Select min (text) from min_max;
Select max (text) from min_max;

Output:

Examples of SQL Min and Max

Different examples are mentioned below:

Example #1

In the below example, we are using the min_max table to show the instance of SQL min, and the max function below is records of the min_max table.

Code:

Select * from min_max;

Output:

The example below finds the most significant and minor number of records from stud_marks tables. We use the min function in the first query, and in the second query, we use the max function.

Code:

Select min (stud_marks) from min_max;
Select max (stud_marks) from min_max;

Output:

Example #2

In the below example, we are using a single column with min and max functions and the where condition.

Code:

Select min (stud_marks) from min_max where id = 10;
Select max (stud_marks) from min_max where id = 20;

Output:

Example #3

In the below example, we are using two columns in the query and the stud_marks column in the min and max function to find the smallest and largest number.

Code:

Select id, min (stud_marks) from min_max group by id;
Select id, max (stud_marks) from min_max group by id;

Output:

Example #4

In the below example, we use min and max functions in a single query as follows.

Code:

Select min (id), max (stud_marks) from min_max;

Output:

Conclusion

A function is straightforward to use. It will be recording the minimum and maximum number from the record set. The function finds the smallest and largest value from the column. For example, the SQL min function returns the smallest value from the specified column.

Recommended Articles

This is a guide to SQL Min and Max. Here we discuss the introduction, SQL min, and max functions and examples. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. SQL EXCLUDE
4. MySQL InnoDB Cluster