I was just reading Darrell Norton's Blog about the
Ultimate Resume. I always use the same question to find out how much someone knows about Oracle SQL.
Here it is.
Given a table called tab1 which has a single column called col1 which 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
It is amazing how many people think it is dead easy. "Just select distinct col1 from tab1" They say. It is not that easy.
I'll post the answer in a couple of days time.
Lets see who can come up with the answer before then. Post your answers as comments.
Fun eh?
Posted by chrismatts at May 15, 2004 6:11 PMHmm. I don't have an Oracle database handy, so I did this with Postgres, and it came out exactly right with the distinct query.
I also can do: select col1 from tab1 group by col1;
I'll be interested to see why this doesn't work in Oracle.
Posted by: John Brothers at May 16, 2004 12:27 PMHmmmm ... this bothers me .... I can't think why "distinct" doesn't work ... could it be something to do with the column type?
If "distinct" doesn't work then I guess you're going to have to group by col1.
Hmmmm ...
Posted by: Clarke Ching at May 18, 2004 4:40 PMdelete col1 from tab1 a where rowid < (select min(rowid) from tab1 b where a.clo1 = b.col1
Posted by: Rajesh at May 18, 2004 9:58 PMHOW CAN WE DROP DUAL TABLE
Posted by: raj at June 9, 2004 8:50 AM