how to delete the duplicate records from a table?

Question

how to delete the duplicate records from a table?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

sherifffruitfly (11/27/2009)
I'm no sql genius or anything, but is anyone concerned that the poster apparently has 2+ versions of the truth running around in his or her database?
Besides the technical detail of how to delete rows, shouldn't attention be placed on how multiple versions of the truth appeared in the first place, and which one is correct? (And then questions like setting keys properly, adequate normalization, etc. get asked?)

Some people like to delve into this level of detail. For the most part, most of us will only do it when we feel the OP is about to make a serious mistake or go down a very bad path. For example, we'll usually require an explanation before we give someone code that could seriously damage their database. Answering their question (again, so long as we don't see a lot of very obvious harm that could come from it) is priority one, but the forum is all about sharing knowledge. Maybe you could be the person who attempts to give people advice on these types of things.

You should prepare yourself for a lot of unanswered questions though, many people stop checking a thread once their question has been answered.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

sherifffruitfly (11/27/2009)
I'm no sql genius or anything, but is anyone concerned that the poster apparently has 2+ versions of the truth running around in his or her database?
Besides the technical detail of how to delete rows, shouldn't attention be placed on how multiple versions of the truth appeared in the first place, and which one is correct? (And then questions like setting keys properly, adequate normalization, etc. get asked?)

In addition to what SQLGuru and Seth has to say about this, i really liked the signature of Paul White which I want to refer here which says "The quality of the answers is directly proportional to the quality of the question. "

(Hope Paul white wont mind referring or borrowing this for this instance!)

---------------------------------------------------------------------------------

rudyhinojosa2007 SSC Rookie Points: 46 More actions · Answer 3

You can also make use of the Rank() function of SQL 2005 and 2008...

so if you have a table called dbo.table_1 and it has duplicates in the name field:

select * from (

select *, rank() over (partition by [name] order by [data]) as rank

from (select * from dbo.table_1 ) sm

) tmp where rank = 1