SQL select only rows with max value on a column

Another manner to do the job is using `MAX()` analytic function in OVER PARTITION clause

SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev

The other `ROW_NUMBER()` OVER PARTITION solution already documented in this post is

SELECT t.*
FROM
(
SELECT id
,rev
,contents
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable
) t
WHERE t.rank = 1

This 2 SELECT work well on Oracle 10g.

MAX() solution runs certainly FASTER that `ROW_NUMBER()` solution because `MAX()` complexity is `O(n)` while `ROW_NUMBER()` complexity is at minimum `O(n.log(n))` where `n` represent the number of records in table !