For this type of problem I like the Windows functions to give each row a number which makes the row unique.

With CTE the 'double' rows can be removed.

CREATE TABLE dbo.SB_Dupli

(

Tekst varchar(300) NULL,

Number int NULL

) ON [PRIMARY]

insert into SB_Dupli values ('aaaa', 1)

insert into SB_Dupli values ('aaaa', 6)

insert into SB_Dupli values ('aaaa', 1)

insert into SB_Dupli values ('bbbb', 2)

insert into SB_Dupli values ('bbbb', 2)

insert into SB_Dupli values ('bbbb', 2)

insert into SB_Dupli values ('cccc', 3)

insert into SB_Dupli values ('dddd', 4)

insert into SB_Dupli values ('dddd', 5)

insert into SB_Dupli values ('dddd', 4)

-- show all rows.

select * from sb_dupli

-- show all distinct rows.

select distinct * from sb_dupli

-- show all rows with duplicates

select Tekst, number, COUNT(*) multiple_aantal from SB_Dupli

group by Tekst, number having COUNT(*) > 1

-- give the duplicates a 'number'

-- remove the double rows.

--------------------------------------------------------

-- Actual code to delete

--------------------------------------------------------

;with

A as (

select

row_number() OVER(PARTITION BY tekst, number ORDER BY (SELECT NULL)) volgnr

from sb_dupli

)

delete A where volgnr > 1

--------------------------------------------------------

-- End Actual code to delete

--------------------------------------------------------

-- show the table

select * from sb_dupli

-- drop the example table

drop table SB_Dupli

Variations. (Replace the row_number line with)

-- Duplicate texts are removed.

row_number() OVER(PARTITION BY tekst ORDER BY (SELECT NULL)) volgnr

-- Duplicate texts are removed Highest number is left.

row_number() OVER(PARTITION BY tekst ORDER BY number DESC) volgnr

-- Duplicate texts are removed Lowest number is left.

row_number() OVER(PARTITION BY tekst ORDER BY number) volgnr

-- Duplicate number are removed.

row_number() OVER(PARTITION BY number ORDER BY (SELECT NULL)) volgnr

With this set of code you can remove duplicates where the complete row is duplicated or a number of fields is duplicated.

The partition clause should contain all the fields to determine which are duplicates, first row in the order by is the row which is kept.

Ben