Oracle equivalent of Postgres’ DISTINCT ON?

by

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
) foo
where 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.

Leave a Comment