Get unique row based on 2 columns with duplicate values-CodePudding

I have a table with 3 columns and 6 rows:

PROBLEM

As you can see based on the highlighted red text, Ash and Joey have the same Last name and Street address i.e. column "Last" and column "Street" have a duplicate value. I would like to only get one of them.

Desired result would be to get rows without duplicate values on the "Last" and "Street" columns:

RESULT

Where only one of Ash or Joey is retained (I just used Ash in this example, but Joey would be fine too - just need 1 or the other, not both).

Is this even possible? Any advise appreciated, thanks.

CodePudding user response：

Since you don't care which record of the duplicates survives you can give this a shot. It'll actually keep the first one alphabetically by First

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (First VARCHAR(255), Last VARCHAR(255), Street VARCHAR(255));
INSERT #t SELECT 'Ash', 'Williams', '123 Main';
INSERT #t SELECT 'Ben', 'O''Shea', '456 Grand';
INSERT #t SELECT 'Claire', 'Port', '543 Jasper';
INSERT #t SELECT 'Denise', 'Stone', '543 Jasper';
INSERT #t SELECT 'Erica', 'Thomas', '789 Holt';
INSERT #t SELECT 'Joey', 'Williams', '123 Main';

WITH dupes AS (
    SELECT First,
           Last,
           Street,
           ROW_NUMBER() OVER (PARTITION BY Last, Street ORDER BY First) RowNum
    FROM #t
)
SELECT First, Last, Street
FROM dupes
WHERE RowNum = 1;

CodePudding user response：

On the assumption you want to retain the person with the first name alpabetically, you can use the ROW_NUMBER window function to generate a new row number for each duplicate and use that to filter out the dupes:

CREATE TABLE Peeps
(
    FirstName   NVARCHAR(20),
    LastName    NVARCHAR(20),
    Street      NVARCHAR(20)
)

INSERT INTO Peeps
VALUES
('Ash','Williams','123 Main'),
('Ben','O''Shea','456 grand'),
('Claire','Port','543 Jasper'),
('Denise','Stone','543 Jasper'),
('Erica','Thomas','789 Holt'),
('Joey','Williams','123 Main')


SELECT  FirstName,
        LastName,
        Street
FROM    (
            SELECT  FirstName,
                    LastName,
                    Street,
                    ROW_NUMBER () OVER (PARTITION BY LastName,Street ORDER BY FirstName) AS RowN
            FROM    Peeps
        ) a
WHERE   RowN = 1


DROP TABLE Peeps