SQL command to remove not distinct records in MS SQL

Posted on 2009-02-23
Last Modified: 2012-05-06
How can I delete those records with the same ID, I just want to retain unique record in one particular column.


I have these records:

custID    FirstName                   LastName
1           Jose                          Fabella
1          James                        Smith
2          Sharon                      Williams
3          Nick                           Joaquin
2          Tim                            Burton

After deleted, it should retain 1,2,3 records - it's irrelevant who will be deleted on the duplicate custID as long as it will retain one of each

Question by:jr_bautista
    LVL 25

    Assisted Solution

    Try -

    DELETE t1
    FROM tablename t1
    LEFT JOIN (SELECT CustId, MIN(FirstName) as MinFirstName, MIN(LastName)  as MinLastName, COUNT(*)  DupCount 
    	   FROM tablename GROUP BY CustId HAVING COUNT(*) > 1) t2 
    	ON t2.CustId = t1.CustId and t2.MinFirstName = t1.FirstName and t2.MinLastName = t1.LastName
    WHERE t2.CustId IS NULL

    Open in new window

    LVL 22

    Accepted Solution

    What is/are the key(s) in your table? It generally helps to specify the keys when asking such a question. Try:

    WITH t AS
     (SELECT custID, FirstName, LastName,
     ROW_NUMBER() OVER (PARTITION BY custID ORDER BY FirstName, LastName) r
     FROM tbl)
    DELETE FROM t WHERE r > 1;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now