Delete duplicate rows but keep original [message #338836] |
Wed, 06 August 2008 01:38  |
adit_me1
Messages: 49 Registered: October 2007 Location: BANGALORE
|
Member |
|
|
Hi,
I was trying to delete duplicate rows in a table while keeping one copy for each case. I was able to select the rows using rank(), row_number() functions but could not figure out how I can delete the rows.
here is what i have tried
select * from t1;
RN
----------
20
18
16
16
16
13
12
10
7
5
4
select rn,rnk from (select rn, row_number()over(partition by rn order by rn desc)rnk from t1)
where rnk >1
RN RNK
---------- ----------
16 2
16 3
now i wish to delete the above 2 rows from T1. I tried various delete statements but somehow its not clicking as to what should be the where condition in my delete command???
delete from t1 where rnk in
(
select rnk from (select rn, row_number()over(partition by rn order by rn desc)rnk from t1)
where rnk >1
)
|
|
|
|
|
|
|
|
|
|