Sometimes, we want to select only rows with max value on a column with SQL.

In this article, we’ll look at how to select only rows with max value on a column with SQL.

How to select only rows with max value on a column with SQL?

To select only rows with max value on a column with SQL, we can use the MAX function.

For instance, we write

SELECT a.id, a.rev, a.contents
FROM table a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM table 
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

to seelct the rows with the max valur with the subquery result that we joined YourTable WITH.

And then we select the required columns from YourTable with

SELECT a.id, a.rev, a.contents
FROM table a

We do the inner join on the id and rev columns with

ON a.id = b.id AND a.rev = b.rev

Conclusion

To select only rows with max value on a column with SQL, we can use the MAX function.