SQL select only rows with max value on a column

Posted by Kyle Hankinson April 14, 2023

---

SQL Select Only Rows with Max Value on a Column

Introduction

In SQL, selecting rows with the maximum value in a column is a common task. This article demonstrates how to accomplish this using standard SQL. The focus is on retrieving all rows that contain the maximum value in a specific column.

Understanding the Requirement

Imagine you have a table named Sales with columns ProductID, SaleDate, and Amount. Your goal is to find the product(s) with the highest sale amount.

SQL Queries to Achieve the Goal

Using a Subquery

The most straightforward method is to use a subquery that finds the maximum value, and then select rows that match this maximum value.

SELECT *
FROM Sales
WHERE Amount = (
    SELECT MAX(Amount)
    FROM Sales
);

This query selects all rows from Sales where Amount equals the maximum Amount found in the table.

Using JOIN

Another approach is to use a JOIN. This is particularly useful when working with large datasets or when additional filtering is required.

SELECT s1.*
FROM Sales s1
JOIN (
    SELECT MAX(Amount) AS MaxAmount
    FROM Sales
) s2 ON s1.Amount = s2.MaxAmount;

Here, we create an inner query to find the maximum sale amount and then join it with the original Sales table to get the corresponding rows.

Using Window Functions (For SQL Databases Supporting Them)

If your SQL database supports window functions, you can use the RANK() or DENSE_RANK() functions.

SELECT ProductID, SaleDate, Amount
FROM (
    SELECT *,
           RANK() OVER (ORDER BY Amount DESC) as rnk
    FROM Sales
) t
WHERE rnk = 1;

This query ranks the sales by Amount in descending order and then selects the rows with the top rank.

Conclusion

Selecting rows with the maximum value in a column is a versatile skill in SQL. Depending on your specific requirements and the features supported by your SQL database, you can choose the method that best suits your needs.

Remember, the key is to understand your data and choose the most efficient query that provides the desired results.

---

Note: The SQL syntax used in this article is based on standard SQL. Depending on your database system (like MySQL, PostgreSQL, SQL Server, etc.), slight modifications might be necessary.