Articles Cookbook
Back to list
Standard SQL

Finding Maximum Values in Rows

Database:

Operators:

MAX, WHERE

Problem:

You want to find which rows 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.

idfirst_namelast_namegrade
1LisaJackson3
2GaryLarry5
3TomMichelin2
4MartinBarker2
5EllieBlack5
6MarySimpson4

Let’s find the students who have the highest grades.

Solution:

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

Here’s the result:

idfirst_namelast_namegrade
2GaryLarry5
5EllieBlack5

Discussion:

To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student). The main query displays id, first and last name, 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: WHERE grade = (SELECT MAX(grade) FROM student)).

Recommended courses:

Recommended articles:

See also:

go to top