我现在想把不是唯一的记录删除，只保留一条，应该用什么语句？

我的UNCTX表里有1000多条数据，我用select count(distinct recode) from unctx语句查询出来，竟然有600条数据是重复的，我现在想把recode不是唯一的记录删除，只保留一条，应该用什么语句？
---------------------------------------------------------------

你可以用create table unctr_bak as select distinct * from unctx;
create table unctr_old as select * from unctx;(将旧的数据保存下来)
truncate table unctr;
insert into table unctx as select * from unctr_bak;

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

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY pkcol);
---------------------------------------------------------------

delete from t1 where rowid in
(select max(rowid) from t1 group by c1,c2,....
having count(*) > 1 )

一句话:-)

(但是效率是差的)
---------------------------------------------------------------

create or replace view v_aa(aa)
as
select col
from tables
group by col;

select count(*) from v_aa;

有些麻烦，不过可以得到结果。嘿嘿

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

http://www.oradb.net/sql/find0.htm
---------------------------------------------------------------

delete from unctx a
where rowid<(select max(rowid) from unctx where recode=a.recode);
不过当表较大时（例如：50万),这个方法会叫人难以忍受
---------------------------------------------------------------

hosia(红霞飞) 已经把2种方法列出来了！
---------------------------------------------------------------

DELETE FROM our_table
WHERE rowid not in
(SELECT MAX(rowid)
FROM our_table
GROUP BY record);
同意bzszp
---------------------------------------------------------------

DELETE FROM our_table WHERE rowid not in
(SELECT MIN(rowid) FROM our_table GROUP BY pkcol);