delete duplicates of 3 column combination

select COUNT(*),COL1,COL2,COL3 from tblOCDELStudent_1011 group by COL1,COL2,COL3 having COUNT(*) >1

gives records that have the combination of COL1,COL2,COL3 more than once..

how can we eliminate duplicates that all of them will have exactly one count only.. that is, the above query should give no results at all, and so we can delete ..

if the count is 3 it should have 2 deletes.. and if 4, then 3 and so forth..
LVL 5
25112Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
Can you provide a description of the tblOCDELStudent_1011 table?
Scott PletcherSenior DBACommented:
Is there an identity column or other unique value on each row?

If not, you might have to use a cursor with TOP.
AshokSr. Software EngineerCommented:
Try

delete t
from tblOCDELStudent_1011 t
where  pk = (select top 1 pk, COUNT(*),COL1,COL2,COL3 from tblOCDELStudent_1011 group by COL1,COL2,COL3 having COUNT(*) >1);
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott PletcherSenior DBACommented:
If there IS an identify / unique value:

delete
from tblOCDELStudent_1011
from tblOCDELStudent_1011 oc
inner join  (
    select COUNT(*),COL1,COL2,COL3, MIN(uniqueCol) AS uniqueCol
    from tblOCDELStudent_1011
    group by COL1,COL2,COL3
    having COUNT(*) >1
) AS dups ON
    dups.col1 = oc.col1 AND
    dups.col2 = oc.col2 AND
    dups.col3 = oc.col3 AND
    dups.uniqueCol < oc.uniqueCol

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
If you have a primary key PK_ID then it could be something like this
--delete tblOCDELStudent_1011 
select *  -- first test it as select, then change it to delete
from  tblOCDELStudent_1011  as t1
inner join (
select COL1,COL2,COL3,min(PK_ID)  as MIN_PK_ID
from tblOCDELStudent_1011 
group by COL1,COL2,COL3 having COUNT(*) >1
) as t2 on t1.col1 = t2.col1
 t1.col2 = t2.col2
 t1.col3 = t2.col3
 t1.PK_ID <> t2.MIN_PK_ID

Open in new window

Scott PletcherSenior DBACommented:
If there is NOT an identify / unique value:

declare csrDups cursor fast_forward for
select COUNT(*),COL1,COL2,COL3
from tblOCDELStudent_1011
group by COL1,COL2,COL3
having COUNT(*) >1

open crsDups

while 1 = 1
begin
    fetch next from csrDups INTO @count, @col1, @col2, @col3
    delete top (@count - 1)
    from tblOCDELStudent_1011
    where col1 = @col1 and col2 = @col2 and col3 = @col3
end --while

deallocate csrDups
jogosCommented:
obvious some AND's missing
--delete tblOCDELStudent_1011 
select *  -- first test it as select, then change it to delete
from  tblOCDELStudent_1011  as t1
inner join (
select COL1,COL2,COL3,min(PK_ID)  as MIN_PK_ID
from tblOCDELStudent_1011 
group by COL1,COL2,COL3 having COUNT(*) >1
) as t2 on t1.col1 = t2.col1
AND  t1.col2 = t2.col2
AND  t1.col3 = t2.col3
AND  t1.PK_ID <> t2.MIN_PK_ID

Open in new window

awking00Information Technology SpecialistCommented:
If there is no identifying field (i.e. primary key), you can also accomplish what you need by the following:

create table tmp as
select <column_list> from
(select <column_list>, row_number() over (partition by col1, col2, col3 order by 1) rn
 from tblOCDELStudent_1011)  as x
where x.rn = 1;

truncate table tblOCDELStudent_1011

insert into tblOCDELStudent_1011 select * from tmp

drop table tmp
AshokSr. Software EngineerCommented:
Here is tested solution in SQL Server 2008.....

DECLARE @Totals As Int;
set @Totals  =
(
select Top 1 Dups.cnt As Cnt from
DailyLunch a
inner join
(
select Count(*) cnt, Menu_Date from DailyLunch
group by Menu_Date
having COUNT(*) >1
) As Dups ON
dups.Menu_Date = a.Menu_Date
)
DECLARE @Dt AS DateTime;
set @Dt =
(
select Top 1 Dups.Menu_Date As MnuDt from
DailyLunch a
inner join
(
select Count(*) cnt, Menu_Date from DailyLunch
group by Menu_Date
having COUNT(*) >1
) As Dups ON
dups.Menu_Date = a.Menu_Date
)
Delete Top (@Totals-1) from DailyLunch
where Menu_Date = @Dt;

Note: By the way, I have only 1 column in Group by, but just change it to include 3 columns there.

HTH
Ashok
25112Author Commented:
let me try your solutions..

the pk is the 3 columns ... col1/col2/col3 combination.. (of course, PK is not enforced yet.. that is the goal of this project )
25112Author Commented:
the table structure is

COL1 varchar      12
COL2 datetime      
COL3 varchar      4
COL4 numeric      9
COL5 char      1
COL6 varchar      4
COL7 varchar      4
COL8 varchar      4
COL9 char      1
COL10 varchar      30
COL11 char      1
25112Author Commented:
awking00, your syntax gives this issue..
Windowed functions do not support integer indices as ORDER BY clause expressions.


scott, i am getting stuck in declaring your variables right.. i am working on it..

ashok- your last query - is that with PK or without..?
awking00Information Technology SpecialistCommented:
Change order by 1 to order by col1, col2, col3
AshokSr. Software EngineerCommented:
ashok- your last query - is that with PK or without..?

without PK

when you have
Group by Col1, Col2, Col3

it will find duplicates based on these 3 columns.

By the way, my solution works, but it will NOT delete all duplicates in one SHOT.  Still learning everyday.

To use my solution, you may have to write Stored Procedure and use Cursor (loop), etc.

Ashok
AshokSr. Software EngineerCommented:
Here is my revised version (which has combined two SELECTs in one!)

DECLARE @Totals As Int;
DECLARE @Dt AS DateTime;
WITH x AS
(
select Top 1 Dups.cnt As Cnt, Dups.Menu_Date As MnuDt from
DailyLunch a
inner join
(
select Count(*) cnt, Menu_Date from DailyLunch
group by Menu_Date
having COUNT(*) > 1
) As Dups ON
dups.Menu_Date = a.Menu_Date
)
SELECT
    @Totals = Cnt,
    @Dt = MnuDt
FROM x
-- select @Totals, @Dt      << Uncomment only for Testing purpose

Delete Top (@Totals-1) from DailyLunch
    where Menu_Date = @Dt;

By the way, my solution works, but it will NOT delete all duplicates in one SHOT.  Still learning everyday.
To use my solution, you may have to write Stored Procedure and use Cursor (loop), etc.

HTH
Ashok
Deepak ChauhanSQL Server DBACommented:
I think it will return the expected result ,,,,, you can analysis data by this.

select col1, col2, col3 from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a --where row >1

and after that  if you are satisfied then....

delete from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a where row >1
AshokSr. Software EngineerCommented:
Deepak,

select col1, col2, col3 from (
select row_number() over( partition by col1 order by col1
) as row, col1, col2, col3
from table_A) a where row > 1;

and after that  if you are satisfied then....

delete a from (
select row_number() over( partition by col1 order by col1
) as row, col1, col2, col3
from table_A) a where row > 1;

You are missing "a" near the delete as shown above.

HTH
Ashok
Deepak ChauhanSQL Server DBACommented:
Ashok

As according you above.
You are missing "a" near the delete as shown above.

But my dear adding a column name or  with Delete Keyword can cause an error

Msg 208, Level 16, State 1, Line 1
Invalid object name  

i think delete syntax  is "(DELETE from tablename where value =,<,>,in,between) like this.
awking00Information Technology SpecialistCommented:
Did you try my method with the change in the order by clause?
25112Author Commented:
ashok/awking00

your syntax worked.. many thanks.. i tweaked scott's also to work.. (I did it in transaction and rolled back to test)
25112Author Commented:
thanks for the idea Deepak
AshokSr. Software EngineerCommented:
Deepak,

delete from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a where row >1;

Above does NOT work.
Do not say "I think....".

I tested it in SQL 2008 Management Studio.
After adding "b", it worked.

If you test it, you will know.

Thanks,
Ashok
AshokSr. Software EngineerCommented:
By the way if you also add "a" to select like this....

select a.Menu_Date from
(
select row_number() over(partition by Menu_Date order by Menu_Date
) as row, Menu_Date
from DailyLunch) a where row > 1;

It works.

Ashok
Deepak ChauhanSQL Server DBACommented:
Ashok

you r right

it is working with 'a'

i apologize
sorry for being mistake
AshokSr. Software EngineerCommented:
Deepak,

It's ok.  We all make mistakes.
Sometimes it is better to test before making any judgements.

Thanks,
Ashok
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.