![Sql Select Only Rows with Max Value on a Column [Duplicate]](https://docuprint.cloud68.co/uploads/sql-select-only-rows-with-max-value_1200.jpg)
I have this table for documents (simplified version here):
| id | rev | content |
|---|---|---|
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
How do I select one row per id and only the greatest rev?
With the above data, the result should contain two rows: [1, 3, ...] and [2, 1, ..]. I'm using MySQL.
Currently I use checks in the while loop to detect and over-write old revs from the resultset. But is this the only method to achieve the result? Isn't there a SQL solution?
27 Answers
At first glance...
All you need is a GROUP BY clause with the MAX aggregate function:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
