Re: Skill testing SQL question

/* delete all dplicate data, only one representant remains */ delete from dup
where rowid not in (
  select min (sub_id)
  from
   (
   select rowid sub_id, aa, bb
   from dup
   )
  group by aa, bb
  );

argosy22_at_my-deja.com schrieb:

> 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?
>
> Happy puzzling,
>
> Argosy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------