How to find rows with maximum value in SQL

Problem

You have a table with numerous rows, and you need to determine which rows contain the maximum value for a certain column.

Sample Data

Consider the following sample table named Employee:

In this table, we want to find the row(s) with the maximum salary.

Solution

SELECT * FROM Employee
WHERE Salary = (
  SELECT MAX(Salary) FROM Employee
);

Explanation

The SQL query above consists of two parts:

1. The subquery (SELECT MAX(Salary) FROM Employee) computes the maximum salary from the Employee table.
2. The main query SELECT * FROM Employee WHERE Salary = ... selects the rows from the Employee table where the salary equals the maximum salary computed by the subquery.

Hence, this query will return all rows from the Employee table where the salary is maximum. In the sample data provided, it will return both Alice and Bob as they both have the maximum salary of 6000.