create table qxjtest(msgid varchar2(20),content varchar2(20));
insert into qxjtest values('2003','a');
insert into qxjtest values('2003','a');
insert into qxjtest values('2003','f');
insert into qxjtest values('2004','b');
insert into qxjtest values('2004','c');
insert into qxjtest values('2005','d');
insert into qxjtest values('2006','e');
commit ;delete qxjtest
where rowid in
(
select rowid from (
select msgid,
content,
lag(msgid,1) over (order by msgid) as msgid1
from qxjtest order by msgid
) a
where a.msgid=a.msgid1
)
insert into qxjtest values('2003','a');
insert into qxjtest values('2003','a');
insert into qxjtest values('2003','f');
insert into qxjtest values('2004','b');
insert into qxjtest values('2004','c');
insert into qxjtest values('2005','d');
insert into qxjtest values('2006','e');
commit ;delete qxjtest
where rowid in
(
select rowid from (
select msgid,
content,
lag(msgid,1) over (order by msgid) as msgid1
from qxjtest order by msgid
) a
where a.msgid=a.msgid1
)
delete from table where id in (select max(id) from table where count(msgid)>1))
where rowid in
(select t.rid
from (select rowid rid,row_number() over (partition by msgid order by msgid) rnb
from table_name) t
where t.rnb > 1);