Функция SQL MAX()
Оператор SQL MAX() — функция возвращающая максимальное значение столбца таблицы.
Функция SQL MAX() имеет следующий синтаксис:
Примеры оператора SQL MAX: Имеется следующая таблица Universities :
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
| 3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
| 4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
| 6 | Ural Federal University | 57000 | 19 | 5640 | Yekaterinburg | urfu.ru |
| 7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Пример 1: Используя оператор SQL MAX найти максимальное значение колонки Students:
Пример 2: Используя оператор SQL MAX найти максимальное значение колонки Faculties, где расположение университета (Location) — Saint-Petersburg:
Пример 3: Используя оператор SQL MAX найти университет (UniversityName) с наибольшим числом преподавателей (Professores):
Как вывести максимальное значение в sql
MAX() — это одна из агрегатных функций, доступных в SQL, которая помогает получить наибольшее значение среди нескольких значений, указанных в столбце.
Например, команда ниже вернёт из таблицы univerities название университета с максимальным количеством студентов и само количество студентов
Как найти максимальное значение sql
The Min() function is used to return the minimum value of the selected columns, and The Max() function is used to return the maximum value of the selected columns.
In this article, we will look for several functionalities depending on min and max functions.
SQL MIN() Function
SQL min function returns the smallest value in the column. The MIN() function provides the smallest value of the chosen column. similarly, the max function will return the max value from the records.
Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
Parameter Explanation
- MIN() function will return the minimum value from all records.
SQL MAX() Functions
SQL max function returns the largest value in the column. The MAX() function provides the largest value of the chosen column. SQL max functions return the largest value in the column.
The MAX() function provides the largest value of the chosen column. similarly, the min function will return the min value from the records. In this article, we will already see MIN and now we will look into MAX functions in SQL.
Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Let’s assume that we have one table with the name”emp” and we have to fetch the person with min age.
SQL CREATE Table:
Output:
Query:
Output:
The following SQL statement finds the highest Age:
Query:
Output:
Using MIN() and MAX() with Other Columns
Suppose we want to fetch a person’s name with min age as column min_age then we can use the following query.
Query:
Output:
Using MIN() or MAX() In the HAVING Clause
Suppose we want to fetch a person’s name with max age as column max_age then we can use the following query but with some conditions like min age at least 22Query:
Output:
Conclusion
The SQL aggregate functions MIN() and MAX() are widely used. Here we described various applications for them in this article. The primary distinction between them is that while MAX() returns the maximum value, MIN() locates the minimum value among a set of values.
3 Ways to Select the Row with the Maximum Value in SQL
Here are three examples that use SQL to find and select the row with the maximum value in a given column.
The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.
Sample Data
We’ll start with the following data:
Option 1
Here’s an example of selecting the row with the maximum value from the Score column in the above table:
We used the MAX() function within a subquery to find the maximum value, and returned the whole row with the outer query.
When there are Multiple Rows with the Max Value
Using this method, if there’s more than one row with the max value, all of them are returned.
Suppose we insert another row into our table with the same score as the existing max score:
Our table now looks like this:
We can see that both Wag and Purr have got the highest score of 85.
Let’s run the previous query again to return the maximum value from that column:
Both rows with the max values are returned as expected.
We can limit the result set to just one row if required. The exact code will depend on the RDBMS being used.
The LIMIT clause can be used with RDBSs such as PostgreSQL, MariaDB, MySQL, and SQLite:
In SQL Server, we can use the TOP clause:
And in Oracle Database:
Option 2
If we only want one row returned, we can actually do away with most of the other code and just get the first row out of the ordered results:
And in Oracle Database:
Option 3
Another way to select the row with the maximum value is to join the table on itself, like this:
As with the earlier example, we can limit the results to one row (or some other number) if required.