Oracle中与Postgres的DISTINCT ON相当的功能是什么？

Question

在Oracle中如何查询组内的第一个值？在Postgres中，您可以使用DISTINCT ON查询组内的第一个值。根据Postgres手册所述，SELECT DISTINCT ON (expression [, ...])仅保留指定表达式计算结果相等的一组行中的第一行。 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

- beerbajay

Answer 1

可以使用first_value()函数或rank()或row_number()函数在Oracle中复制相同的效果。

这两种变体在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

用rank()替换row_number()在这个例子中产生相同的结果。

这个变体的一个特点是，它可以用来获取给定分区（例如“最后3个更新”）的前N行，只需将rownumber = 1更改为rownumber <= N。

- beerbajay

---

+1：first_value() 变体是对 DISTINCT ON 的巧妙模拟。我应该将其构建到 jOOQ 中。 - Lukas Eder

如果我想从2个字段中获取DISTINCT和相应的值，并从所有其他字段中获取这些值，该如何应用？ - tumchaaditya

@tumchaaditya - 使用ROW_NUMBER()函数！ - Vérace

Answer 2

如果你有超过两个字段，则使用 beerbajays 的答案作为子查询（请注意以 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
)

- Jeremy Thompson