Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing Duplicates Expand / Collapse
Author
Message
Posted Thursday, January 14, 2010 9:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, January 27, 2010 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, January 28, 2010 7:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, January 28, 2010 7:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, January 29, 2010 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 2, 2010 11:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, February 8, 2010 4:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 5:42 AM
Points: 12, Visits: 83
Would be good to know what are the advantages of this approach?

Thanks

Nick
Post #861538
Posted Monday, February 8, 2010 3:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse