Removing Duplicates

  • Comments posted to this topic are about the item Removing Duplicates

  • This gives me syntax errors

  • 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

  • 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

  • Or even more simply... 🙂

    DELETE D FROM

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

    FROM dbo.T1) D

    where RowNum > 1

  • 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

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

    Thanks

    Nick

  • 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 '?'.

  • 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