How to get duplicate rows - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

How to get duplicate rows

Author

Topic

rukkur
Starting Member

10 Posts

Posted - 2004-09-21 : 06:51:26

How to get duplicate rows from 1 table(name,sal)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-21 : 06:55:59

select name, count(sal)
from table1
group by name
having count(sal) > 1

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 07:01:20

this should help:

select *
from MyTable t1
where 1<(select count(id) from MyTable where t1.id = id)

Go with the flow & have fun! Else fight the flow

rukkur
Starting Member

10 Posts

Posted - 2004-09-21 : 07:06:07

I want to Identify and Delete all duplicate records (only 1 record should remain)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 07:31:49

aha... then the easiest way is to do
#temp table should have same structure as MyTable

insert into #temp(col1 , ...)
select
(select col1 , ...
from MyTable )
union
(select col1 , ...
from MyTable )

delete from MyTable

insert into MyTable(col1 , ...)
select col1 , ...
from #temp

the union operator creates distinct records.
you could also do:

select distinct *
into #temp
from MyTable

and then delete the data from MyTable and insert the data from #temp back in.

Go with the flow & have fun! Else fight the flow

rukkur
Starting Member

10 Posts

Posted - 2004-09-21 : 07:52:22

Thanks , is there any way to do in single query?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 08:05:54

check this out:
http://www.sqlteam.com/item.asp?ItemID=3331

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources