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.