How does one select the TOP N rows from a table?

Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:

SELECT *

FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)

WHERE ROWNUM <>

Use this workaround with prior releases:

SELECT *

FROM my_table a

WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)

FROM my_table b

WHERE b.maxcol >= a.maxcol)

ORDER BY maxcol DESC;