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
 Old Forums
 CLOSED - General SQL Server
 How to get duplicate rows
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rukkur
Starting Member

10 Posts

Posted - 09/21/2004 :  06:51:26  Show Profile
How to get duplicate rows from 1 table(name,sal)

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/21/2004 :  06:55:59  Show Profile  Visit ditch's Homepage
select name, count(sal)
from table1
group by name
having count(sal) > 1



Duane.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/21/2004 :  07:01:20  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

rukkur
Starting Member

10 Posts

Posted - 09/21/2004 :  07:06:07  Show Profile
I want to Identify and Delete all duplicate records (only 1 record should remain)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/21/2004 :  07:31:49  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

rukkur
Starting Member

10 Posts

Posted - 09/21/2004 :  07:52:22  Show Profile
Thanks , is there any way to do in single query?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/21/2004 :  08:05:54  Show Profile  Visit spirit1's Homepage
check this out:
http://www.sqlteam.com/item.asp?ItemID=3331

Go with the flow & have fun! Else fight the flow
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000