How to remove duplicate rows in a table

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

General SQL Server Forums

New to SQL Server Programming

How to remove duplicate rows in a table

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 17:53:43

Hello All,

in my Sql Server(2005) Table, i have data like below

Acct#, Acct Name, BK #, Addr1, Stat#
123,Jr Borello,45,Pau St,345321
124,Ketrina,45,Light DR,341121
125,Steve,51,Corr Dr St,445323
123,Anand,45,Sell St,345321
121,Myaham,41,Park Ave,767333

from the above data,how can i remove the duplicate rows(here in this example duplicate rows means, need not to be same in all column values),

but if Acct#,BK#,Stat# are same(ex: 1st & 4th rows) then how can i remove it?

can any one please assist me

Thanks in advance
asin

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 18:25:43

Sample data and query


declare @sample table (
Acct int, AcctName varchar(50), BKno int, Addr1 varchar(50), Stat varchar(10)
)
insert into @sample
select 123,'Jr Borello',45,'Pau St',345321 union all
select 124,'Ketrina',45,'Light DR',341121 union all
select 125,'Steve',51,'Corr Dr St',445323 union all
select 123,'Anand',45,'Sell St',345321 union all
select 121,'Myaham',41,'Park Ave',767333

delete from @sample
from @sample a, 
(select
acct, 
acctname,
bkno,
addr1,
stat
from (
select
acct, 
acctname,
bkno,
addr1,
stat,
row_number() over (partition by acct, bkno, stat order by stat) as rowid,
count(*) OVER (PArtition by acct, bkno, stat) as cnt
from @sample
)t 
where rowid=1 and cnt>1)b
where a.acct = b.acct and a.acctname = b.acctname
and a.bkno = b.bkno and a.addr1 = b.addr1 and a.stat = b.stat

select * from @sample

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 19:30:55

Hello hanbingl,

Thank you for your fastest reply,

seems to be here you are manually inserting the rows into sample table

anyway i will work on it and let you know

once again thank you very much for your reply

Best Regards
asin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 01:57:58

hanbingl is not manually inserting records. The initial population part is just to create a sample data for your scenario. As you've existing table with data, you need to do only the delete query part.
Also please note that you should be using sql 2005 or later if you want to use row_number() function. just in case you're using sql 2000 or below, use the below query

delete t1 from yourtable t1
left join (select [Acct#], MAX([Acct Name]) AS latest, [BK #], [Stat#]
            from yourtable
            group by [Acct#],[BK #], [Stat#])t2
on t2.[Acct#] = t1.[Acct#]
AND t2.latest =t1.[Acct Name]
AND t2.[BK #] =t1.[BK #]
AND t2.[Stat#]=t1.[Stat#]
WHERE t1.[Acct#] IS NULL

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-25 : 15:37:41

Hello Visakh,

Thank you for your reply,

as i issued the above command it says remove 0 rows,

however if i use only below command the result is (eliminating of duplicates) but if use whole your command it says 0 rows to delete

quote:

select [Acct#], MAX([Acct Name]) AS latest, [BK #], [Stat#]
from yourtable
group by [Acct#],[BK #], [Stat#]

can you please look at it once

Thanks & Regards
asin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:17:35

what does the below return you?

select [Acct#],  [BK #], [Stat#]
            from yourtable
            group by [Acct#],[BK #], [Stat#]
HAVING COUNT(*) >1

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-26 : 23:45:48

quote:

Originally posted by visakh16

what does the below return you?

select [Acct#],  [BK #], [Stat#]
            from yourtable
            group by [Acct#],[BK #], [Stat#]
HAVING COUNT(*) >1

it returns one row

Actual Table Data

123 Andi 45 Lpuram 4353
123 Boochek 45 Kjamna 3222
345 Rjqsa 23 Vpuram 1234
456 mhsan 64 sydney 7384
345 asin 23 usa 1234

with below sql statement it returns one query

quote:
select [Acct], [BKno], [Stat]
from v2ex
group by [Acct],[BKno], [Stat]
HAVING COUNT(*) >1

results below row

345 23 1234

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 00:06:10

quote:
Originally posted by dhani

quote:
Originally posted by visakh16

what does the below return you?
select [Acct#], [BK #], [Stat#]
from yourtable
group by [Acct#],[BK #], [Stat#]
HAVING COUNT(*) >1

it returns one row

Actual Table Data

123 Andi 45 Lpuram 4353
123 Boochek 45 Kjamna 3222
345 Rjqsa 23 Vpuram 1234
456 mhsan 64 sydney 7384
345 asin 23 usa 1234

with below sql statement it returns one query

quote:
select [Acct], [BKno], [Stat]
from v2ex
group by [Acct],[BKno], [Stat]
HAVING COUNT(*) >1

results below row

345 23 1234

then this should delete one row of that group

delete t1 from yourtable t1
left join (select [Acct#], MAX([Acct Name]) AS latest, [BK #], [Stat#]
            from yourtable
            group by [Acct#],[BK #], [Stat#])t2
on t2.[Acct#] = t1.[Acct#]
AND t2.latest =t1.[Acct Name]
AND t2.[BK #] =t1.[BK #]
AND t2.[Stat#]=t1.[Stat#]
WHERE t1.[Acct#] IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 00:15:50

you can even try this:-

delete t
from
(select row_number() over (partition by [Acct#],[BK #],[Stat#] order by [Acct Name] desc) as seq,*
from yourtable
)t
where t.seq>1

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-28 : 10:59:51

quote:

Originally posted by visakh16

you can even try this:-

delete t
from
(select row_number() over (partition by [Acct#],[BK #],[Stat#] order by [Acct Name] desc) as seq,*
from yourtable
)t
where t.seq>1

Hello visakh,

this is worked wonder,

i am very glad,

Thank you very much, this what i am looking for

Best Regards
asin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:10:49

quote:
Originally posted by dhani

quote:
Originally posted by visakh16

you can even try this:-
delete t
from
(select row_number() over (partition by [Acct#],[BK #],[Stat#] order by [Acct Name] desc) as seq,*
from yourtable
)t
where t.seq>1

Hello visakh,

this is worked wonder,

i am very glad,

Thank you very much, this what i am looking for

Best Regards
asin

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources