Re: Skill testing SQL question

 There is a very easy solution to this question. And a very reasonably one by the interviewer.

Solution

delete DUPE_TABLE a
  where rowid = (select max(rowid)

                    from DUPE_TABLE b
                    where b.aa = a.aa
                      and b.bb = b.bb)

On Fri, 5 Nov 1999 08:54:50 Jonathan Lewis wrote:
> Message from the Deja.com forum:
> comp.databases.oracle.server
> Your subscription is set to individual email delivery
>>
>
>It's not a good question, it's either a
>very bad question or a totally inspired
>question.
>
>
>With 6 rows and two columns as set,
>the sensible solution is:
>
>
>select * from dupe_table;
>
>-- examine output by eye
>
>delete from dupe_table where aa = 1 and rownum = 1;
>delete from dupe_table where aa = 2 and rownum = 1;
>delete from dupe_table where aa = 3 and rownum = 1;
>
>select * from dupe_table; -- to check results
>
>commit;
>
>
>If the intent of your interviewer was to
>discover how smart you were at writing
>'clever' SQL, the question was bad.
>
>If the interviewer was trying to find out
>whether you were able to spot pragmatic
>realistic, simple solutions before diving
>for 'esoteric technology' then the question
>was brilliant.
>
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
>argosy22_at_my-deja.com wrote in message <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
>>
>>
>
>
>
>
>
>
> _____________________________________________________________
> Deja.com: Before you buy.
> http://www.deja.com/
> * To modify or remove your subscription, go to
> http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
> * Read this thread at
> http://www.deja.com/thread/%3C941792675.26450.0.nnrp-09.9e984b29%40news.demon.co.uk%3E
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- Share what you know. Learn what you don't.

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Fri Nov 12 1999 - 17:21:36 CST