Arvind's posterous

Interview Questions - SQL (Oracle specific)

How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 6;
SELECT name FROM (SELECT name, row_number() OVER (ORDER BY salary DESC) row) where row <6;

How to delete duplicate rows in Oracle?
DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

or

Query will keep the row with the least rowid. Rows with higher rowid’s will be deleted.
delete from table_name a
where a.rowid > any (select b.rowid
  from
     table_name b
  where
     a.col1 = b.col1
  and
     a.col2 = b.col2)

Find the nth highest salary of an employee?