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

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-l
Received on Fri Jun 28 2013 - 13:50:56 CEST

Original text of this message