Re: Skill testing SQL question

OK, OK

Now that I think about it,
I came up with a solution:

Delete
from dupes
where rowid not IN

   (
    Select rowids.the_rowid
    From

      (Select distinct aa,
                       bb,
                       max(rowid) the_rowid
       From dupes
       Group by aa, bb)	rowids

It uses rowid, and virtual tables in the from clause, a few tools that I don't use
everyday.

In article <7vss7n$c36$1_at_nnrp1.deja.com>,   argosy22_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 04 1999 - 15:38:11 CST