SQL MAX Tutorial
The MAX command is used to find the maximum value in a given result set. It is frequently used with the GROUP BY command.
Example 1 (no aggregation)
-- Works in PostgreSQL, MySQL, SQL Server, and Oracle
SELECT
MAX(dollar_amount) as highest_payment
FROM
rent_payments;
rent_payments table
| id | tenant_id | payment_date | dollar_amount |
|---|---|---|---|
| 1 | 1 | 3/1/2018 | 2000 |
| 2 | 2 | 3/2/2018 | 1500 |
| 3 | 3 | 3/1/2018 | 1800 |
| 4 | 4 | 3/3/2018 | 1900 |
| 5 | 1 | 4/2/2018 | 2000 |
| 6 | 4 | 4/2/2018 | 1900 |
Query results
| highest_payment |
|---|
| 2000 |
Example 2 (with aggregation)
-- Works in PostgreSQL, MySQL, SQL Server, and Oracle
SELECT
payment_date,
MAX(dollar_amount) as highest_payment
FROM
rent_payments
GROUP BY
payment_date;
rent_payments table
| id | tenant_id | payment_date | dollar_amount |
|---|---|---|---|
| 1 | 1 | 3/1/2018 | 2000 |
| 2 | 2 | 3/2/2018 | 1500 |
| 3 | 3 | 3/1/2018 | 1800 |
| 4 | 4 | 3/3/2018 | 1900 |
| 5 | 1 | 4/2/2018 | 2000 |
| 6 | 4 | 4/2/2018 | 1900 |
Query results
| payment_date | highest_payment |
|---|---|
| 3/1/2018 | 2000 |
| 3/2/2018 | 1500 |
| 3/3/2018 | 1900 |
| 4/2/2018 | 2000 |
Explanation
We are finding the MAX value of dollar_amount for each unique value of the payment_date column.