How to Find Rows with Maximum Value

Database:

Operators:

MAX
WHERE

Problem:

You want to find rows which store the largest numeric value in a given column.

Example:

Our database has a table named student with data in the following columns: id, first_name, last_name, and grade. You want to find the students who have the highest grades.

id	first_name	last_name	grade
1	Lisa	Jackson	3
2	Gary	Larry	5
3	Tom	Michelin	2
4	Martin	Barker	2
5	Ellie	Black	5
6	Mary	Simpson	4

Solution:

SELECT id, first_name, last_name, grade
FROM student
WHERE grade = (SELECT MAX(grade) FROM student);

Here’s the result:

id	first_name	last_name	grade
2	Gary	Larry	5
5	Ellie	Black	5

Discussion:

To find the maximum value of a column, use the MAX() aggregate function. The function takes a column name or an expression to find the maximum value. In our example, we use the subquery to find the highest number in the column grade. The subquery is:

SELECT MAX(grade) FROM student

The main query displays ID, first and last name of the student, and their grade. To display only the rows with the maximum value among all values in the column (e.g., SELECT MAX(grade) FROM student), use WHERE with a subquery. In WHERE, put the name of the column with the comparable value to the value returned by aggregate function in the subquery. In our example it is:

WHERE grade = (SELECT MAX(grade) FROM student)

Problem:

Example:

Solution:

Discussion:

Recommended courses:

Recommended articles:

See also: