Re: Logical duplicates
From: rjamya <rjamya_at_gmail.com>
Date: Fri, 28 Jun 2013 07:50:56 -0400
Message-ID: <CAGurbTM3C-jhZqfe5Aa_Nwy54sudYm2gUUVQgm0DgM7gJPBvpA_at_mail.gmail.com>
drop table travel purge;
create table travel (col1 varchar2(10), col2 varchar2(10), dist number); insert into travel values ('NY','LDN','4000');insert into travel values ('LDN','NY','4000');
insert into travel values ('NY','SFO','3500');insert into travel values ('SFO','NY','3500');
insert into travel values ('DFW','FRA','5000');insert into travel values ('FRA','DFW','5000');
commit;
prompt data as is
select * from travel;
prompt modify display so it makes sense to remove dups prompt use this sql to select data so it makes sense to quickly identify dups and delete
prompt select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel
prompt ps: this is akin to finding matching numbers from lottery table :) select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel /
data as is
Date: Fri, 28 Jun 2013 07:50:56 -0400
Message-ID: <CAGurbTM3C-jhZqfe5Aa_Nwy54sudYm2gUUVQgm0DgM7gJPBvpA_at_mail.gmail.com>
drop table travel purge;
create table travel (col1 varchar2(10), col2 varchar2(10), dist number); insert into travel values ('NY','LDN','4000');insert into travel values ('LDN','NY','4000');
insert into travel values ('NY','SFO','3500');insert into travel values ('SFO','NY','3500');
insert into travel values ('DFW','FRA','5000');insert into travel values ('FRA','DFW','5000');
commit;
prompt data as is
select * from travel;
prompt modify display so it makes sense to remove dups prompt use this sql to select data so it makes sense to quickly identify dups and delete
prompt select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel
prompt ps: this is akin to finding matching numbers from lottery table :) select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel /
data as is
COL1 COL2 DIST
---------- ---------- ----------
NY LDN 4000 LDN NY 4000 NY SFO 3500 SFO NY 3500 DFW FRA 5000 FRA DFW 5000
6 rows selected.
use this sql to select data so it makes sense to quickly identify dups and
delete
select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel
ps: this is akin to finding matching numbers from lottery table :)
LOC_1 LOC_2 DIST
---------- ---------- ----------
LDN NY 4000 LDN NY 4000 NY SFO 3500 NY SFO 3500 DFW FRA 5000 DFW FRA 5000
now writing a delete becomes much more trivial
Raj
On Fri, Jun 28, 2013 at 7:43 AM, rjamya <rjamya_at_gmail.com> wrote:
> Care to tell us *how* or which part of it did not work?
>
>
>
> On Fri, Jun 28, 2013 at 5:54 AM, <rajugaru.vij_at_gmail.com> wrote:
>
>> Hi,
>> This did not work. Can you please let me know alternatives?
>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 28 2013 - 13:50:56 CEST