SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 distinct in select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helixpoint
Constraint Violating Yak Guru

276 Posts

Posted - 01/15/2013 :  18:57:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3583 Posts

Posted - 01/15/2013 :  19:10:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/16/2013 :  23:44:56  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000