Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Skill testing SQL question
In article <7vtd5e$gh$1_at_news5.svr.pol.co.uk>,
"-=< a q u a b u b b l e >=-"
<aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com> wrote:
> <argosy22_at_my-deja.com> wrote in message news:7vss7n$c36
$1_at_nnrp1.deja.com...
> > Hello all,
> >
> > This is a good skill testing question
> > that I was asked in an interview.
> >
> > You have a table, DUPE_TABLE,
> > with two fields, AA, and BB.
> > No primary key. Dupe data.
> > Only 6 rows.
> > 2 sets of the same exact 3 rows.
> >
> > DUPE_TABLE:
> > AA BB
> > 1 x
> > 2 y
> > 3 z
> > 1 x
> > 2 y
> > 3 z
> >
> >
> > You want to delete 3 rows, so that
> > you have only one set of 3:
> >
> > AA BB
> > 1 x
> > 2 y
> > 3 z
> >
> >
> > To date, I haven't been able to
> > figure out the answer.
> > Does anyone have any ideas
> > how to delete these dupe rows
> > with SQL code?
>
> Better to use EXISTS or = rather than IN or >.
>
> If you know that you only have doubles then you can use = ...
>
> i.e.
>
> DELETE
> FROM dupe_table d_main
> WHERE rowid = (SELECT MIN(rowid)
> FROM dupe_table d_sub
> WHERE d_sub.aa = d_main.aa
> AND d_sub.bb = d_main.bb)
>
It's classic "REMOBE DUPLICATES" question.
You can use
DELETE
FROM dupe_table d_main
WHERE rowid > (SELECT MIN(rowid)
FROM dupe_table d_sub WHERE d_sub.aa = d_main.aa AND d_sub.bb = d_main.bb)
to remove all duplicates ( even if you have more than 2 )
HTH. Michael.
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Nov 05 1999 - 08:05:46 CST