使用GROUP BY后MRD_NO列仍存在重复行的去除方法咨询

Hey there! Let's break down why you're seeing duplicate MRD_NO values and how to fix it.

The Root Cause #

Your current query uses GROUP BY MRD_NO, RESOURCE_NAME, which creates a separate group for every unique combination of these two columns. That's why you're getting multiple rows for the same MRD_NO—each row ties to a different RESOURCE_NAME linked to that MRD_NO. To eliminate duplicate MRD_NO entries, we need to select only one RESOURCE_NAME per MRD_NO before (or during) the aggregation process.

Solution: Use Window Functions to Prioritize Rows #

The most flexible way to handle this is with the ROW_NUMBER() window function. It lets you assign a unique number to each row within a group (here, each MRD_NO group) based on your preferred sorting rule—like prioritizing the 'Murugan' RESOURCE_NAME, or picking the first one alphabetically.

Here's the modified query:

WITH RankedEMRRecords AS (
    SELECT 
        MRD_NO,
        RESOURCE_NAME,
        -- Simplified diagnosis combining using ISNULL instead of CASE WHEN (same effect)
        Diagnosis = STUFF((
            SELECT DISTINCT ', ' + ISNULL(Diagnosis, OTHER_DIAGONSIS)
            FROM EMR_master b 
            WHERE b.MRD_NO = a.MRD_NO 
            FOR XML PATH('')
        ), 1, 2, ''),
        -- Assign row numbers per MRD_NO: prioritize 'Murugan' first, then alphabetical order
        RowRank = ROW_NUMBER() OVER (
            PARTITION BY MRD_NO 
            ORDER BY 
                CASE WHEN RESOURCE_NAME = 'Murugan' THEN 0 ELSE 1 END,
                RESOURCE_NAME ASC
        )
    FROM EMR_master a 
    WHERE a.TREATMENT_CODE IN ('CC','PO','SRE','REG')
)
-- Only keep the top-priority row for each MRD_NO
SELECT MRD_NO, RESOURCE_NAME, Diagnosis
FROM RankedEMRRecords
WHERE RowRank = 1
ORDER BY RESOURCE_NAME, MRD_NO;

How This Works #

1. CTE with Row Ranking: The RankedEMRRecords CTE first calculates the combined diagnosis string for each MRD_NO (just like your original query, but simplified with ISNULL). Then it uses ROW_NUMBER() to number rows within each MRD_NO group:
   • Rows where RESOURCE_NAME is 'Murugan' get a RowRank of 0 (so they come first)
   • All other rows are sorted alphabetically by RESOURCE_NAME and numbered sequentially
2. Filter for Unique MRD_NO: By selecting only rows where RowRank = 1, we ensure each MRD_NO appears exactly once—with your preferred RESOURCE_NAME if it exists, otherwise the first alphabetical one.

Alternative: If You Don't Need a Specific RESOURCE_NAME #

If you just want any single RESOURCE_NAME per MRD_NO (no preference), simplify the ORDER BY in the window function to:

ORDER BY RESOURCE_NAME ASC

This will pick the alphabetically first RESOURCE_NAME for each MRD_NO.

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