Deleting Duplicates
Dan, August 21, 2007 - 4:59 pm UTC
Well, if by "duplicate records" you mean that all of the column values in each row are the same, you could "select them out" using the row_number function. For example:
SQL> create table xxx (a1 varchar2(1), b2 varchar2(1), c3 varchar2(1));
Table created.
SQL> insert into xxx values ('a','b','c');
1 row created.
SQL> insert into xxx values ('a','b','c');
1 row created.
SQL> insert into xxx values ('a','b','c');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xxx;
A B C
- - -
a b c
a b c
a b c
SQL> create table zzz as
2 (select a1, b2, c3
3 from
4 (select a1, b2, c3, row_number() over
5 (partition by a1,b2,c3 order by a1,b2,c3) rn
6 from xxx)
7 where rn=1);
Table created.
SQL> select * from zzz;
A B C
- - -
a b c
Then, just drop xxx and rename zzz to xxx.
August 22, 2007 - 12:33 pm UTC
that is what I describe as one of the options in the in the link - not using analytics - just select distinct * from t; would do it.
Duplicate Records
Dan, August 21, 2007 - 5:11 pm UTC
Sorry, the test data in the last example was a little weak. This is a bit better:
SQL> create table xxx (a1 varchar2(1), b2 varchar2(1), c3 varchar2(1));
Table created.
SQL> insert into xxx values ('a','b','c');
1 row created.
SQL> insert into xxx values ('a','b','c');
1 row created.
SQL> insert into xxx values ('a','b','c');
1 row created.
SQL> insert into xxx values ('d','e','f');
1 row created.
SQL> insert into xxx values ('d','e','f');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xxx;
A B C
- - -
a b c
a b c
a b c
d e f
d e f
SQL> create table zzz as
2 (select a1, b2, c3
3 from
4 (select a1, b2, c3, row_number() over
5 (partition by a1,b2,c3 order by a1,b2,c3) rn
6 from xxx)
7 where rn=1);
Table created.
SQL> select * from zzz;
A B C
- - -
a b c
d e f
August 22, 2007 - 12:35 pm UTC
just use distinct, analytics complicate things here - not appropriate for this.
To Dan
A reader, August 21, 2007 - 10:08 pm UTC
Dan, Can you not just do,
create table zzz as
select distinct * from xxx;
??
Thanks.
August 22, 2007 - 1:28 pm UTC
yes.