Oracle эквивалент Postgres DISTINCT ON?

В postgres вы можете запросить первое значение в группе с помощью DISTINCT ON. Как это можно достичь в Oracle?

В руководстве postgres:

SELECT DISTINCT ON (выражение [,...]) содержит только первую строку каждый набор строк, где данные выражения оцениваются равными. Выражения DISTINCT ON интерпретируются с использованием тех же правил, что и для ORDER BY (см. Выше). Обратите внимание, что "первая строка" каждого набора непредсказуемый, если ORDER BY не используется, чтобы гарантировать, что желаемая строка появляется первым.

Например, для данной таблицы:

 col1 | col2 
------+------
 A    | AB
 A    | AD
 A    | BC
 B    | AN
 B    | BA
 C    | AC
 C    | CC

Сортировка по возрастанию:

> select distinct on(col1) col1, col2 from tmp order by col1, col2 asc;
 col1 | col2 
------+------
 A    | AB
 B    | AN
 C    | AC

Убывающая сортировка:

> select distinct on(col1) col1, col2 from tmp order by col1, col2 desc;
 col1 | col2 
------+------
 A    | BC
 B    | BA
 C    | CC

Ответ 1

Тот же эффект может быть реплицирован в Oracle либо с помощью функции first_value(), либо с помощью одного из rank() или row_number().

Оба варианта также работают в Postgres.

first_value()

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

first_value дает первое значение для раздела, но повторяет его для каждой строки, поэтому необходимо использовать его в комбинации с distinct, чтобы получить одну строку для каждого раздела.

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

Замена row_number() на rank() в этом примере дает тот же результат.

Особенностью этого варианта является то, что его можно использовать для извлечения строк first N для данного раздела (например, "последние 3 обновления" ), просто изменив rownumber = 1 на rownumber <= N.

Ответ 2

Если у вас есть более двух полей, используйте ответ на пивоварения в качестве вспомогательного запроса (примечание в порядке DESC):

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