Post reply

Removing Duplicates

  • January 14, 2010 at 9:55 pm

    #214601

    Comments posted to this topic are about the item Removing Duplicates

  • January 27, 2010 at 3:34 pm

    #1109636

    This gives me syntax errors

  • January 28, 2010 at 7:29 pm

    #1110423

    You are right about the systax errors, it was happening as I had some empty characters in the query I attached. I am reattaching the script w/o the empty characters.

    Also, to note that this script is compatible only with SQL Server 2005 versions or newer.

    Thanks

  • January 28, 2010 at 7:35 pm

    #1110425

    Forgot to past the script:

    DELETE D FROM

    (

    SELECT ProductName, ROW_NUMBER()OVER(ORDER BY ProductName) AS RowNum

    FROM dbo.T1

    ) as D

    JOIN

    (

    SELECT ProductName, ROW_NUMBER()OVER(ORDER BY ProductName) AS RowNum

    FROM T1

    ) as E

    on D.ProductName = E.ProductName

    AND D.RowNum < E.RowNum

  • January 29, 2010 at 4:15 pm

    #1111064

    Or even more simply... 🙂

    DELETE D FROM

    (SELECT ProductName, ROW_NUMBER()OVER(ORDER BY ProductName) AS RowNum

    FROM dbo.T1) D

    where RowNum > 1

  • February 2, 2010 at 11:17 am

    #1112440

    I believe you meant to say...partition it by ProductName and delete all the records greater than rownum 1.

    You probably missed to add the Partition By in the Over clause. Yes, this is great too and less code.

    DELETE D FROM

    (SELECT ProductName, ROW_NUMBER()OVER(PARTITION BY ProductName ORDER BY ProductName) AS RowNum

    FROM dbo.T1) D

    where RowNum > 1

  • February 8, 2010 at 4:07 am

    #1115375

    Would be good to know what are the advantages of this approach?

    Thanks

    Nick

  • February 8, 2010 at 3:31 pm

    #1115836

    I copied out of the article and try to run the scripts. After multiple errors I gave up.It would be an advantage to publish scripts which are working instead of producing error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '?'.

  • May 19, 2016 at 1:14 pm

    #1880346

    Thanks for the script.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply