Getting Minimum and Maximum Value in MySQL

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> −

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.

AmitDiwan

Updated on: 09-Mar-2021

987 Views

Kickstart Your Career

Get certified by completing the course

Get Started