How to Find Rows with Maximum Value

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.

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

Solution:

```SELECT id, first_name, last_name, grade
FROM student
```

Here’s the result:

2GaryLarry5
5EllieBlack5

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)`