In this tutorial I will explain how to calculate max and min in sql with sql MAX function.
To perform the queries I will use the SQL SELECT query in several examples.
Also, I’m going to show you examples on MAX and SELECT from simpler to advanced so that you can find the solution to your problem.
Content
- How to calculate max in SQL?
- Know the SQL SELECT query
- Using the SQL MAX() function
- Examples of MAX SQL with SELECT
- SELECT with plain MAX SQL
- SQL MAX and GROUP BY example
- Advanced example of MAX and HAVING
- SELECT MAXSQL? What else?
How to calculate max in SQL?
To be able to calculate the maximum in SQL you will need to have a couple of clear knowledge:
- How the SELECT SQL query is used
- Know how to use SQL functions and specifically the MAX() function
Know the SQL SELECT query
As a quick review, the structure of a SELECT is:
SELECT columna1, columna2, ... FROM tabla [WHERE condiciones]
Using the SQL MAX() function
The SQL MAX function It is used in the same way as all SQL functions: by calling it by its name together with the parentheses.
The MAX SQL function syntax is:
MAX( columna )
The function MAX calculates the maximum between the values in the column specified. Technically we say that it returns an “aggregate record” since it returns a result row of the calculation among all the rows provided by the query.
Knowing this let’s see the examples.
Examples of MAX SQL with SELECT
Now let’s get down to business, let’s see several examples of queries with MAX.
SELECT with plain MAX SQL
The following function would return a row with a column “max_age” with the maximum value among all the rows in the “user” table.
SELECT MAX(edad) as edad_maxima FROM usuario;
SQL MAX and GROUP BY example
Now imagine what we want to know What is the maximum age of the students in each course? from a university.
We have these students in a table called “students” with the columns age, course, name, ID.
We could make use of the following query:
SELECT MAX(edad) as edad_maxima, curso FROM alumnos GROUP BY curso;
The result of this query would be one record per course with the highest value of the age column for each course.
Because?
Well, because I have grouped by course and in this way I get the maximum for each course. An example of the result would be:
maximum_age | course |
---|---|
5 | 1st |
6 | 2nd |
7 | 3rd |
8 | 4th |
9 | 5th |
Advanced example of MAX and HAVING
Finally I am going to leave you a more advanced query, making use of the max SQL function and the SQL HAVING clause. Specifically, I am going to select those courses that have at least one student who is over the age of 30:
SELECT c.* FROM alumno a INNER JOIN curso c ON a.id_curso = c.id GROUP BY c.id HAVING MAX(a.edad) > 30;
SELECT MAXSQL? What else?
Well I hope this helped you post about SQL MAX function. If so, it would help us a lot if you shared this content or left a comment that helps other users and motivates us to continue publishing.
Thank you!