表temp: jh rq ly
7P3 2008-5-6 23
7P3 2008-5-19 21
7P4 2008-5-3 12
7P8 2008-5-9 25
7P8 2008-5-20 18
7P8 2008-5-29 14
现想将重复记录删掉,并保留最新日期的记录
jh rq ly
7P3 2008-5-19 21
7P4 2008-5-3 12
7P8 2008-5-29 14
请各位指教,该如何做 谢谢~
7P3 2008-5-6 23
7P3 2008-5-19 21
7P4 2008-5-3 12
7P8 2008-5-9 25
7P8 2008-5-20 18
7P8 2008-5-29 14
现想将重复记录删掉,并保留最新日期的记录
jh rq ly
7P3 2008-5-19 21
7P4 2008-5-3 12
7P8 2008-5-29 14
请各位指教,该如何做 谢谢~
delect *
from temp a
where not exists
(
select 1
from
(
select b.rowid row_id,row_number() over(partition by b.jh order by rq desc) rn
) c
wehre c.rn=1 and c.row_id=a.rowid)
WHERE EXISTS (SELECT 1
FROM (SELECT ROWID
,row_number() over(PARTITION BY jhORDER BY rq DESC) rn
FROM temp) t2
WHERE rn <> 1
AND t1.ROWID = t2.ROWID)
其实和hebo2005的一样...
from temp a
where not exists
(
select 1
from
(
select b.rowid row_id,row_number() over(partition by b.jh order by rq desc) rn
from temp b
) c
wehre c.rn=1 and c.row_id=a.rowid)
ora-03114:未连接到oracle
这是怎么回事?