Removing Duplicates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bdba » Removing Duplicates

Removing Duplicates

Rate Topic

Display Mode

Topic Options

Author

Message

bdba

Posted Thursday, January 14, 2010 9:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319

Comments posted to this topic are about the item Removing Duplicates

Post #848077

rcassis-1001343

Posted Wednesday, January 27, 2010 3:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 18, 2010 5:15 PM
Points: 6, Visits: 8

This gives me syntax errors

Post #854795

bdba

Posted Thursday, January 28, 2010 7:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319

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

Post #855720

bdba

Posted Thursday, January 28, 2010 7:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319

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

Post #855722

rcassis-1001343

Posted Friday, January 29, 2010 4:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 18, 2010 5:15 PM
Points: 6, Visits: 8

Or even more simply... :)

DELETE D FROM
(SELECT ProductName, ROW_NUMBER()OVER(ORDER BY ProductName) AS RowNum
FROM dbo.T1) D
where RowNum > 1

Post #856478

bdba

Posted Tuesday, February 02, 2010 11:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319

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

Post #858145

nick-1043370

Posted Monday, February 08, 2010 4:07 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, January 11, 2013 3:50 AM
Points: 12, Visits: 82

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

Thanks

Nick

Post #861538

Julien.Chappel

Posted Monday, February 08, 2010 3:31 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 18, 2011 8:08 PM
Points: 10, Visits: 44

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

Post #862095

« Prev Topic | Next Topic »

Permissions