delete rows with no exact difference

hi!

i have a problem and here is the scenario:

TABLE1
pkeyno transNo categ gr1 gr2 gr3 gr4
1 123 categ1 2.0 2.3 3.0 2.0
2 123 categ1 2.0
3 123
4 456 categ1 1.0 2.5
5 456 1.0 2.5

this should be really like this:
TABLE1
pkeyno transNo categ gr1 gr2 gr3 gr4
1 123 categ1 2.0 2.3 3.0 2.0
4 456 categ1 1.0 2.5

I need to delete the rows that are duplicates. the problem is there is no exact difference.

is this situation possible to do in an sql statement?

Here you are:

For SS2005

Code Snippet

create table #del (pkeyno int, transNo int, categ varchar(20),

gr1 numeric(9,1), gr2 numeric(9,1), gr3 numeric(9,1), gr4 numeric(9,1) )

insert into #del

select 1, 123, 'categ1', 2.0, 2.3, 3.0, 2.0

union all select 2, 123, 'categ1', 2.0, null, null, null

union all select 3, 123, null, null, null, null, null

union all select 4, 456, 'categ1', 1.0, 2.5, null, null

union all select 5, 456, null, 1.0, 2.5, null, null

;with cte as

(

select *, row_number() over(partition by transNo order by pkeyno) as rn

from #del

)

delete d

from #del d

where exists (select * from cte where rn > 1 and d.pkeyno = cte.pkeyno)

OR

delete d

from #del d

inner join cte

on cte.rn > 1

and d.pkeyno = cte.pkeyno

For SS2000

Code Snippet

delete d

from #del d

where pkeyno not in

(

select min(pkeyno) as pkeyno

from #del

group by transNo

)

|||

I suggest you delete the dirty data first.

then

delete from test where pkeyno in
(
select pkeyno from test as T
where pkeyno >(select min(pkeyno) from test where transNo=T.transNo)
)