使用聚合函数筛选最小时间戳记录，如何避免重复JOIN与WHERE子句？

Absolutely! Repeating that same JOIN and WHERE logic is such a pain—not only does it make your query longer than it needs to be, but it’s also a maintenance headache if you ever have to tweak the filters or join conditions later. Let’s fix that with a few cleaner approaches:

Option 1: Use a Common Table Expression (CTE) #

CTEs let you define a reusable dataset at the start of your query, so you don’t have to repeat the join and filter logic multiple times. Here’s how it works:

WITH ke_customer_data AS (
    SELECT 
        abc_detail.*, 
        abc_cust.*
    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'
)
SELECT *
FROM ke_customer_data
WHERE CREATION_TIMESTAMP = (SELECT MIN(CREATION_TIMESTAMP) FROM ke_customer_data);

The ke_customer_data CTE encapsulates all your join and filter logic once. You can reference it in both the main query and the subquery to get the earliest timestamp, keeping your code DRY (Don’t Repeat Yourself) and easy to update.

Option 2: Use a Window Function (ROW_NUMBER()) #

If you only need the single earliest record (and want a more efficient query that scans the data once instead of twice), use ROW_NUMBER() to rank records by their creation timestamp:

SELECT *
FROM (
    SELECT 
        abc_detail.*, 
        abc_cust.*,
        ROW_NUMBER() OVER (ORDER BY abc_detail.CREATION_TIMESTAMP ASC) AS record_rank
    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'
) ranked_customers
WHERE record_rank = 1;

• ROW_NUMBER() assigns a unique rank to each record, starting at 1 for the earliest CREATION_TIMESTAMP.
• If there are multiple records with the same earliest timestamp and you want to keep all of them, replace ROW_NUMBER() with RANK() or DENSE_RANK() instead.

A quick note: Using SELECT * might lead to duplicate column names (e.g., both tables might have an ID column). For production code, it’s better to explicitly list the columns you need instead of using *.

内容的提问来源于stack exchange，提问作者user2102665