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