Agile Business Coach

My Favourite SQL Question : The Answer

Given a table called tab1 that has a single column called col1 that has the following contents:

col1
----
A
A
B
B
B
C
C

Write a single SQL statement that removes all the duplicates and leaves the following contents:

col1
----
A
B
C

The "SELECT distinct col1 FROM tab1" returns a distinct list but it does not remove the duplicates. To remove the duplicates, you have to use a DELETE statement. But what do you delete? There is no way to differentiate each row.

This is where it gets really nasty. You need to know about ROWID in Oracle which is a hidden primary key on all Oracle tables.

As Clarke suggested, you need to use a group by as well. One answer is:

DELETE FROM tab1 t1
WHERE t1.rowid NOT IN
(SELECT MAX
FROM tab1 t2
GROUP BY col1)

Posted by chrismatts at May 18, 2004 6:46 PM