Re: Skill testing SQL question

<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)