如何依据其他列的值筛选唯一（Distinct）值？

Got it, let's break down how to solve this deduplication problem exactly as you need it. The key rule here is: for each Phase group, we keep New_value if it exists in that group—if not, we stick with Old_value. Here are practical solutions using two common data processing tools:

Assuming your data is stored in a table named your_table, we can use window functions to rank rows within each Phase group, giving priority to entries with New_value.

WITH ranked_data AS (
    SELECT 
        Description,
        Source,
        -- Rank rows: New_value gets priority (rank 1), Old_value gets rank 2
        ROW_NUMBER() OVER (
            PARTITION BY LEFT(Source, CHARINDEX(' ', Source))  -- Group by "Phase 1", "Phase 2" etc.
            ORDER BY CASE WHEN Source LIKE '%New_value' THEN 1 ELSE 2 END
        ) AS row_rank
    FROM your_table
)
-- Pick only the top-ranked row from each Phase group
SELECT Description, Source
FROM ranked_data
WHERE row_rank = 1;

This query first groups your data by the Phase part of the Source column, then ranks each row so New_value entries come first. Finally, we select just the highest-ranked row per group to get your desired unique values.

If you're working with a Pandas DataFrame, we can create a priority flag, sort by Phase and priority, then drop duplicates to keep only the highest-priority entry per group.

import pandas as pd

# Example DataFrame matching your input
df = pd.DataFrame({
    'Description': ['', '', '', '', '', ''],
    'Source': [
        'Phase 1 Old_value',
        'Phase 1 Old_value',
        'Phase 1 New_value',
        'Phase 2 Old_value',
        'Phase 2 Old_value',
        'Phase 2 Old_value'
    ]
})

# Extract the Phase group from the Source column
df['Phase'] = df['Source'].str.extract(r'(Phase \d+)')
# Assign priority: 1 for New_value (higher priority), 2 for Old_value
df['priority'] = df['Source'].map(lambda x: 1 if 'New_value' in x else 2)

# Sort by Phase and priority, then keep only the first entry per Phase
result = df.sort_values(['Phase', 'priority'])\
           .drop_duplicates('Phase', keep='first')\
           [['Description', 'Source']]

print(result)

Running this will output exactly your expected result:

Description           Source
2                     Phase 1 New_value
3                     Phase 2 Old_value

The core idea across both solutions is grouping by Phase, prioritizing New_value over Old_value, and retaining only the highest-priority entry per group—this avoids the unwanted 3-row result you'd get with a simple DISTINCT call.

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