Sql-server – Delete duplicates with specific columns as identifier

duplicationsql serversql-server-2005

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512))..

My amateur-ish SQLfoo lead me to this non-functioning query (for testing reasons I just selected the duplicates)

SELECT num, com FROM TableA
WHERE num, com IN
    (
        SELECT count(*) AS co FROM TableA WHERE co > 1 GROUP BY num, com
    )

How can I delete duplicates from TableA identified by num and com?

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

edit

from the suggestions I've tried the following two queries. TableA features ~220 entries, where ~80 are duplicates.
first @dimitars suggestion 

select ROW_NUMBER() OVER (PARTITION BY [num], com ORDER BY [num], com) as 'RowNumber', t1.*
    into ##TableA
    from TableA t1  

delete t1
--select t1.* 
from TableA  t1
join ##TableC t2
    on t2.[num]=t1.[num] and t2.com=t2.com
where t2.RowNumber > 1

resulting in every entry being deleted. And @marcelo miorellis suggestion

select * from TableA;
with cte as (
    select *, row_number() over (partition by [num], com order by [num], com ) as Picker
    from TableA
    )
select cte 
where Picker > 1

which gives me

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Picker'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'cte'.

Best Answer

--=======================================================
-- delete the duplicate records from table @t
-- keeping a single unit of each
-- marcelo miorelli 24-nov-2014
--=======================================================


--=======================================================
--create a table variable and insert records in it
-- just for this example
--=======================================================
declare @t table ([num] int, com varbinary(512) )

insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 3, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )

--=======================================================
-- this is going to show the table before the deletes
--=======================================================
select * from @t


--=======================================================
-- this deletes the duplicates - leaving only 1 of each
--=======================================================
; with cte as (
    select *
        , row_number() over (partition by [num],com  order by [num],com ) as Picker
    from @t
    )
delete cte 
where Picker > 1


--=======================================================
-- this shows the table after the deletion - without the deletes
--=======================================================
select * from @t
Related Question