Click here to Skip to main content
15,882,063 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
My table has values like this

1 Yahoo
1 Yahoo
2 Google
2 Google
2 Google

I need to delete all duplicate records keeping one record. how do i do it?
Posted
Comments
Subrata.In 19-Nov-12 5:14am    
Why you are not using a id field in your table?
If it's not possible to add field in your table, you can do it by temporary table/variable.

Here is a sample approach
SQL
CREATE TABLE #SampleTable
(
	ID INT,
	[Name] VARCHAR(100)
)

INSERT INTO #SampleTable	
SELECT 1, 'Yahoo' UNION ALL
SELECT 1, 'Yahoo' UNION ALL
SELECT 2, 'Google' UNION ALL
SELECT 2, 'Google' UNION ALL
SELECT 2, 'Google'

--Before Deleting the duplicate rows
SELECT * FROM #SampleTable;

WITH CTE AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name]) AS Row, * FROM #SampleTable
)


DELETE FROM CTE
WHERE ROW <> 1

--After Deleting the duplicate rows
SELECT * FROM #SampleTable

DROP TABLE #SampleTable
 
Share this answer
 
Comments
bolshie6 19-Nov-12 5:52am    
Thanks. works.
__TR__ 19-Nov-12 5:54am    
You are welcome :)
bolshie6 19-Nov-12 6:07am    
Nd can u tell me how actually the select part inside the CTE works?
__TR__ 19-Nov-12 6:12am    
CTE stands for common table expression.
Check out Using Common Table Expressions[^]
More resources here[^]
bolshie6 19-Nov-12 6:15am    
i've used CTEs previously... i did not know about OVER clause. i googled it and learned. Thank u. :)
This question has occurred to developers time and again and there are hundreads of question and articles on this if you search on Google/yahoo ;-)

On CP itself, we have two articles which gives two different ways

How to remove duplicate rows in SQL Server 2008 when no key is present[^]

Remove Duplicate Rows from a Table in SQL Server[^]

Hope taht helps.

Milind
 
Share this answer
 
Comments
bolshie6 19-Nov-12 5:22am    
Thank u for the answer. but i need to do it with out using a %%physloc%%, or adding an IDcol..

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900