-1

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.

P.S. the “Street” column is actually on a different table so the picture of the graph represents 2 tables already joined.

hwight
  • 29
  • 3
  • 1
    Welcome to Stackoverflow. Please do not post images of data. Post sample data and desired results in text format, preferrably including DDL and DML statements so we can use them to quickly duplicate your data on our local instances which helps us help you. – squillman Oct 05 '21 at 19:53
  • Why do you retain Ash but not Joey? What is the criteria? – SE1986 Oct 05 '21 at 19:57
  • It doens't have to be Ash, either retaining 1 of Ash -OR- Joey would be fine. – hwight Oct 05 '21 at 20:00
  • I've used Count(*) > 1 to get duplicates but it's not working with 2 columns, or i don't know how to make it work. – hwight Oct 05 '21 at 20:02
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Oct 06 '21 at 00:49

2 Answers2

1

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;
squillman
  • 13,363
  • 3
  • 41
  • 60
  • Thank you. How can I do a partition on the “Street” column with a join? The “Street” is in a different table than the names. – hwight Oct 06 '21 at 05:42
  • Well, you should have put that requirement in your question. You can edit your question to add that in. The JOIN doesn't change the partitioning other than changing the table reference of the partition column. – squillman Oct 06 '21 at 12:34
0

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
SE1986
  • 2,534
  • 1
  • 10
  • 29