Re: Skill testing SQL question

<michael_bialik_at_my-deja.com> wrote in message news:7vuo7k$lnr$1_at_nnrp1.deja.com...
> 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 )

Yes that's right. Hopefully you'll know what your data is like before you do a data fix like this. Seeing as he only had 2 of each, it is most appropriate (faster) to use =. Received on Fri Nov 05 1999 - 19:04:59 CST