Oracle equivalent of Postgres' DISTINCT ON?

The same effect can be replicated in Oracle either by using the first_value() function or by using one of the rank() or row_number() functions.

Both variants also work in Postgres.

first_value()

select distinct col1, first_value(col2) over (partition by col1 order by col2 asc)from tmp

first_value gives the first value for the partition, but repeats it for each row, so it is necessary to use it in combination with distinct to get a single row for each partition.

row_number() / rank()

select col1, col2 from (  select col1, col2,   row_number() over (partition by col1 order by col2 asc) as rownumber   from tmp) foowhere rownumber = 1

Replacing row_number() with rank() in this example yields the same result.

A feature of this variant is that it can be used to fetch the first N rows for a given partition (e.g. "last 3 updated") simply by changing rownumber = 1 to rownumber <= N.

If you have more than two fields then use beerbajays answer as a sub query (note in DESC order):

select col1,col2, col3,col4 from tmp where col2 in(select distinct first_value(col2) over (partition by col1 order by col2 DESC) as col2from  tmp--WHERE you decide conditions)