Re: Skill testing SQL question

Arati Vijay <vijayj00_at_singnet.com.sg> wrote in article <7vu9v8$a3d$1_at_coco.singnet.com.sg>...
: TRY THIS
:
: CREATE TABLE NO_DUPE AS (SELECT DISTINCT * FROM DUPE_TABLE)
: DROP TABLE DUPE_TABLE
: ALTER TABLE NO_DUPE RENAME TO DUPE_TABLE
:
: Incidentally, this is a question that I also ask at interviews, and
ain't
: got an answer to date.
The solution using DDL statements is, obviously, the worst one. There is a well-known solution, which, as it appeared, comes to mind to most Oracle freshmen worth of employing, though it takes them some time. People with some experience usually know it.

DELETE DUPE_TABLE D1 WHERE EXISTS
 (SELECT 1 FROM DUPE_TABLE WHERE AA=D1.AA AND BB=D1.BB    AND ROWID>D1.ROWID); Of course, if the DUPE_TABLE is huge and not indexed properly, the above query works eternally, so it is worth to create a non-unique index on (AA, BB) in this case.

-- 
 Alexander I.Doroshko, aid_at_grant.kharkov.ua
 * Bank "Grant", Kharkov, Ukraine


: 

: <argosy22_at_my-deja.com> wrote in message


news:7vss7n$c36$1_at_nnrp1.deja.com...


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