Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 distinct in select

Author  Topic 

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-01-15 : 18:57:19
I have 3 fields, One of the fields is an email address. I have to delete the duplicates. I don't care which record I delete. How do I do this

Dave
Helixpoint Web Development
http://www.helixpoint.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 19:10:23
Use row_number function for example like this:
;WITH c AS 
(
SELECT ROW_NUMBER() OVER(PARTITION BY col1, col2 ORDER BY col1) AS N
FROM ThreeColumnTable
)
DELETE
FROM c
WHERE N > 1;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 23:44:56
actually you can use it inline no need of CTE

DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Email ORDER BY NEWID()) AS Seq
FROM Table
)t
WHERE Seq>1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -