使用聚合函数根据最小时间戳筛选记录

有几种方法可以获得最早的记录,并避免两次输入相同的条件。

使用fetch first rows(从Oracle 12c开始提供)

select * 
from abc_customer_details cd
join abc_customers c on c.id = cd.customer_id
where cd.country_code = 'KE'
order by creation_timestamp
fetch first row only;

使用CTE(WITH子句)

with cte as
(
  select * 
  from abc_customer_details cd
  join abc_customers c on c.id = cd.customer_id
  where cd.country_code = 'KE'
)
select *
from cte
where (creation_timestamp) = (select min(creation_timestamp) from cte);

使用窗口函数

select *
from
(
  select cd.*, c.*, min(creation_timestamp) over () as min_creation_timestamp
  from abc_customer_details cd
  join abc_customers c on c.id = cd.customer_id
  where cd.country_code = 'KE'
)
where creation_timestamp = min_creation_timestamp;

(顺便说一下,我更改了所有这些查询中的连接条件。你似乎不太可能加入 abc_customer_details.id = abc_customers.customer_id )

你可以利用 MIN() 分析函数。

SELECT
    *
FROM
    (
        SELECT
            abc_detail.*,
            abc_cust.*,
            MIN(creation_timestamp) OVER(
                PARTITION BY abc_detail.id
            ) AS min_timestamp
        FROM
            abc_customer_details abc_detail
            INNER JOIN abc_customers abc_cust
        ON abc_detail.id = abc_cust.customer_id
        WHERE
            abc_detail.country_code = 'KE'
    )
WHERE
    creation_timestamp = min_timestamp;