Delete duplicate data from table

[bmacbmac]

Hello,

I have the following table:

create table #temp (relatedid int, recordid int, docno varchar(20), bookno varchar(20), pageno varchar(20))
insert into #temp values (1, 89, '12345', '100', '444')
insert into #temp values (2, 89, '12345', '100', '444')
insert into #temp values (3, 89, '12345', '100', '444')
insert into #temp values (4, 90, '55555', '200', '555')
insert into #temp values (5, 90, '55555', '200', '555')
insert into #temp values (6, 91, '77777', '300', '888')
insert into #temp values (7, 91, '77777', '300', '888')
insert into #temp values (8, 91, '77777', '300', '888')
insert into #temp values (9, 91, '77777', '300', '888')

I am looking for a way to remove the duplicate recordid, docno, bookno, pageno values. I don't care which one stays, but I just want one of each instance to stay. So results like:

relatedid, recordid, docno, bookno, pageno
1, 89, '12345', '100', '444'
4, 90, '55555', '200', '555'
6, 91, '77777', '300', '888'


I am writing something that is just turning into a whole bunch of #temp tables and turning into kind of a mess. I think I am making it way to hard.. there has to be a better solution out there. Any ideas?

Thanks!

 

[fredericofonseca]

on this particular case this will do the trick.

assumption, based on the supplied data, is that relatedid is unique

delete t
from #temp t
left outer join (select  min(relatedid) as relatedid
from #temp
group by recordid
       , docno
       , bookno
       , pageno
) t1
on t.relatedid = t1.relatedid
where t1.relatedid is null

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[imex]

Hi,

bmacbmac, you can also try:

;with CTE_RN as
(
    select
        *,
        ROW_NUMBER() OVER(PARTITION BY recordid, docno, bookno, pageno order by relatedid) as RN
    from #temp
)

delete from CTE_RN
where RN > 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]