SQL: Select only rows with Max Value on a Column

Or those who happen to be using a dialect of sql with window functions, selecting the rows where:<p>ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Rev DESC) AS &quot;Row Number&quot;<p>Returns 1 is clearer to my mind, with some caveats around dialects which use separate forms of row-number that assign identical&#x2F;differing numbers to two consecutive identical rows.

I prefer SELECT table.* FROM table WHERE rev = (SELECT MAX(rev) FROM table WHERE id=table.id)<p>I hate joins. Subselects are so much clearer imo - and you don&#x27;t end up with extra fields.