Duplicate Remover

[Vimalsoft Pty Ltd]

i have the Following Query

select sa1 as[sa1],sa2 as [sa2],count(sa1) as [count] into #ttemp from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2

and my final query is this

select c.sa1,c.sa2,c.dur1,c.dur2 from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1
and c.sa2 = t.sa2

as you can see there are Duplicates in the sa1 and sa2. So i want to delete any of record but one of each. so that means at last there should be 5 Records. that bring me these Duiplicate

sa1 sa2 Dur1 der2

6 7 3 2
6 7 3 3
354 867 1 2
354 867 1 3
354 872 1 2
354 872 1 3
356 867 1 2
356 867 1 3
356 872 1 2
356 872 1 3

Thanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Niladri_Biswas]

Just 1 small doubt.. For every record, the

der2

field is different. So how can u say that entire row is a duplicate(though the first 3 are same!)? Please clarify this.. and what is the output u are expecting please put it clearly :)

Niladri Biswas

[Vimalsoft Pty Ltd]

i catch the Duplicate from two Records. i was able to do it like this

select sa1,sa2,count(sa1) from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2

SELECT ACTV as ACTV_ID--, count([VENU]) as , sum([stud])
into #temp
FROM [dbo].SOL_ACTV_VENU
group by ACTV
having count(venu) > 1
order by sum(stud)

and i was deleting my Duplicates like this

SET ROWCOUNT 1
DELETE EXP_REL_CLSH_CONT
from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1
and c.sa2 = t.sa2
SET ROWCOUNT 0

but now in SQL Rowcount = 1 will stop everytime a duplicates is deleted so that it cannot delete the second record. Its Good. But how can i make it to loop and skip one record and delete one in this approach ?

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Niladri_Biswas]

Try this

declare @t table(sal1 int,sal2 int,dur1 int,dur2 int)
insert into @t
select 6,7,3,2 union all select 6,7,3,3 union all
select 354,867,1,2 union all select 354,867,1,3 union all
select 354,872,1,2 union all select 354,872,1,3 union all
select 356,867,1,2 union all select 356,867,1,3 union all
select 356,872,1,2 union all select 356,872,1,3

select * from @t

I am taking this RecordSet

sal1 sal2 dur1 dur2
6 7 3 2
6 7 3 3
354 867 1 2
354 867 1 3
354 872 1 2
354 872 1 3
356 867 1 2
356 867 1 3
356 872 1 2
356 872 1 3

Since you want any one of the record set so I can use either

sal1 sal2 dur1 dur2
6 7 3 2

or

sal1 sal2 dur1 dur2
6 7 3 3

If this assumption of mine is correct, the here is the answer

select sal1,sal2,dur1,dur2 from(
select row_number() over(partition by sal1,sal2 order by sal1,sal2) rn, sal1,sal2,dur1,dur2 from @t) X
where rn = 1

Output:

sal1 sal2 dur1 dur2
6 7 3 2
354 867 1 2
354 872 1 2
356 867 1 2
356 872 1 2

Here I am considering only the first one for every duplicate entries What next you can do is put this record set in some temp table , delete the original one and then insert this record back into the table. Please let me know in case of any concern. Note - This code will work for Sql server 2005+ :)

Niladri Biswas

modified on Tuesday, November 24, 2009 9:31 AM

[Mycroft Holmes]

User row_number partitioned over your key fields. This is a sample of a partition I use ROW_NUMBER() OVER( PARTITION BY ProductID, SubProductID, IssueLabel, Maturity, CurrencyID, Exposure Order by Exposure) as RowNo You need to include the ID field in the rest of the select and then delete any record where the RowNo > 1.