Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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)Received on Thu Nov 04 1999 - 19:50:38 CST