Hello there! In this tutorial, we are going to discuss the use and working of the SQL min() and max() functions.
So let us get right into the topic.
SQL MIN() Function
The SQL min() function is used to find the minimum/lowest value in the given column. The syntax for using the function is given below.
SELECT MIN(column)
FROM Table
WHERE condition;
Where, column and Table are the column name and table name respectively for which we need to find the minimum value. The WHERE condition is used to narrow down the set of values on the basis of the condition.
Let us look at an example to get a better understanding.
Example: The table below contains the score of 3 different students in 3 different subjects(Maths, Science, and English) out of 100.
| Roll | Name | Maths | Science | English |
| 1 | John | 95 | 89 | 90 |
| 2 | Kate | 78 | 90 | 91 |
| 3 | Alex | 87 | 92 | 89 |
Now, let us try to get the minimum marks that a student scored at Maths.
SELECT MIN(Maths)
FROM Marks;
Output:
78
Example – To calculate the overall minimum marks that a student scored out of 300.
SELECT MIN(Maths + Science + English)
FROM Marks;
Output:
259
We get the desired output as Roll 2 got the lowest total marks(78 + 90 + 91 = 259).
SQL MAX() Function
The SQL max() function on the other hand is used to find the maximum/highest value in the given column. The syntax for using the function is given below.
SELECT MAX(column)
FROM Table
WHERE condition;
Similar to the min() function, here too column and Table are the column name and table name respectively for which the maximum value is to be calculated. The WHERE condition is used to narrow down the set of values on the basis of the condition.
Example – Considering the same Marks table used for the min() function, let us this time try to calculate the maximum marks that a student scored in English.
SELECT MAX(English)
FROM Marks;
Output:
91
Example – To calculate the maximum marks that a student scored out of 300 in all the subjects.
SELECT MAX(Maths + Science + English)
FROM Marks;
Output:
274
From the table Marks it is clear that Roll 1 has the highest total marks(95 + 90 + 89 = 274). Hence, the output is justified.
SQL MIN() and MAX() with Alias
Alias in SQL is used to change the display name of a table or column. This feature is very helpful for increasing readability for the users. This is achieved using the AS keyword.
Note: These Aliases are temporary names given for user’s convenience.
1. Alias with MIN()
Let us look at an example.
SELECT MIN(Science) AS Min_Sci_Marks
FROM Marks;
Output:
| Min_Sci_Marks |
| 89 |
As you can see, this time the column name is changed(temporary).
2. Alias with MAX()
Similarly for max() function,
SELECT MAX(Science) AS Max_Sci_Marks
FROM Marks;
Output:
| Max_Sci_Marks |
| 92 |
Summing Up
So that’s it for today. Hope you had a clear understanding of the SQL min() and max() functions. We recommend going through the links given in the references section for more info.
You can also go through our SQL Tutorial.
For any further questions, feel free to use the comments below.