Getting Minimum and Maximum Value in MySQL

MySQLMySQLi Database

We need to use the MAX(columnName) to find the Maximum value in a column, whereas use the MIN(columnName) to find the Maximum value in a column.

Let’s say following is the syntax to find the highest and lowest value in a specific column −

mysql> SELECT @min_val:=MIN(columnName),@max_val:=MAX(columnName) FROM tableName;
mysql> SELECT * FROM tableName WHERE columnName=@min_val OR columnName=@max_val;

Note: Let’s say we have a database named ‘StudentsRecords’ and a table named ‘STUDENT.

Following is our table <STUDENT> −

StudentId
StudentMarks
S001
90
S002
97
S003
72

We will now write the query −

Query

mysql> SELECT @min_val:=MIN(StudentMarks),@max_val:=MAX(StudentMarks) FROM STUDENT;
mysql> SELECT * FROM STUDENT WHERE StudentMarks =@min_val OR StudentMarks =@max_val;

Output

+---------------------+
| StudentMarks        |
+---------------------+
| 97                  |
+---------------------+

In the above query, ‘StudentMarks’refers to the name of the column. The ‘STUDENT’ refers to the name of the table from which the minimum and maximum value is being queried.

raja
Published on 08-Mar-2021 12:26:33
Advertisements