Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

How to remove duplicate rows in SQL Server 2005 when no key is present

, 22 Feb 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Here is another solution:IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL BEGIN DROP TABLE #TestTable ENDCREATE TABLE #TestTable ( Column1 varchar(1), Column2 int )INSERT INTO #TestTable VALUES ('A', 1);INSERT INTO #TestTable VALUES ('A', 1); --...
Here is another solution:
 
IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL
    BEGIN
    DROP TABLE #TestTable
    END
 
CREATE TABLE #TestTable
    (
    Column1 varchar(1),
    Column2 int
    )
 
INSERT INTO #TestTable VALUES ('A', 1);
INSERT INTO #TestTable VALUES ('A', 1); -- duplicate
INSERT INTO #TestTable VALUES ('A', 1); -- duplicate
INSERT INTO #TestTable VALUES ('A', 2);
INSERT INTO #TestTable VALUES ('B', 1);
INSERT INTO #TestTable VALUES ('B', 2);
INSERT INTO #TestTable VALUES ('B', 2); -- duplicate
INSERT INTO #TestTable VALUES ('C', 1);
INSERT INTO #TestTable VALUES ('C', 2);
 
SELECT  *
FROM    #TestTable
ORDER
BY      Column1,
        Column2
 
;WITH dup AS
    (
    SELECT  [DuplicateID] = ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1,Column2 )
    --,*
    FROM    #TestTable
    )
DELETE FROM dup
WHERE   [DuplicateID] > 1
 
SELECT  *
FROM    #TestTable
ORDER
BY      Column1,
        Column2

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Costica U
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralVery good alternative :) Marked as accepted PinmvpMika Wendelius4-Mar-11 9:47 
GeneralReason for my vote of 5 nice trick PinmemberFrancesco Pasquesi1-Mar-11 6:07 
GeneralReason for my vote of 5 Great Query Sir Pinmemberims.sanjay28-Feb-11 19:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 22 Feb 2011
Article Copyright 2011 by Costica U
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid