如何基于DOCDT8列最小值实现WONO、EQMFSN的SQL去重查询

Got it, let's break down how to solve this problem. You want to retrieve unique combinations of WONO and EQMFSN, paired with the earliest (minimum) DOCDT8 value for each of those pairs. Here are two reliable approaches that work across most SQL dialects:

1. Using GROUP BY (Simplest Approach) #

This is the most straightforward method when you only need the distinct pairs and their minimum date:

SELECT WONO, EQMFSN, MIN(DOCDT8) AS earliest_DOCDT8
FROM your_table_name
GROUP BY WONO, EQMFSN;

How it works: #

• The GROUP BY WONO, EQMFSN clause groups all rows together that have identical values for both columns, effectively creating distinct pairs.
• MIN(DOCDT8) calculates the smallest date value within each group, giving you the earliest date associated with that WONO/EQMFSN pair.

2. Using Window Functions (For Additional Columns) #

If you need to include other columns from the row that has the minimum DOCDT8 (not just the date itself), use ROW_NUMBER():

SELECT WONO, EQMFSN, DOCDT8
-- Add any other columns you need from the row here
FROM (
    SELECT 
        WONO, 
        EQMFSN, 
        DOCDT8,
        -- Assign a rank to each row in the WONO/EQMFSN group, ordered by date
        ROW_NUMBER() OVER (PARTITION BY WONO, EQMFSN ORDER BY DOCDT8 ASC) AS row_rank
    FROM your_table_name
) ranked_rows
WHERE row_rank = 1;

How it works: #

• The inner query uses ROW_NUMBER() to assign a sequential number to each row within a partition of WONO and EQMFSN. Rows are ordered by DOCDT8 ascending, so the earliest date gets a rank of 1.
• The outer query filters for rows where row_rank = 1, giving you the single row with the earliest date for each distinct WONO/EQMFSN pair.

Important Note: #

Don't forget to replace your_table_name with the actual name of your database table.

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