Oracle top-n SQL

Question:  I need to write a query that performs a top-10 SQL, and I hear that top-n queries are tricky in Oracle.  What are the methods to get top-n rows back in Oracle SQL?

Answer:  Oracle top-n SQL is tricky!  Yes, being declarative, there are many ways to get top-n queries in Oracle SQL.  Oracle top-n query can be satisfied in several ways:

• Using rownum outside an in-line view
• Using rank with over
• Using row_number with over

Here is a review of the top-n SQL methods in Oracle:

• fetch first n rows:   (12c and beyond):  fetch first rows is an easy way to dislay the top-n rows.   Here is an example is using the fetch first n rows syntax in SQL where we fetch the top 10 employees by salary:

select
   emp_name,
   salary
from
   emp
order by
   salary desc
fetch first 10 rows only;

• Top-n SQL method - Use a subselect with ROWNUM.  You can use an inline view with ROWNUM to get the top-10 rows for any SQL query, and the rownum can be used to materialize an in-line view.  Beware, this is not always a good thing, as adding the where rownum WITH clause. 
  
  
• Top-n method 1 - Use the SQL dense_rank and SQL ranking functions.  Oracle SQL includes ranking functions that provide support for common OLAP rankings, such as the top 10, bottom 10, top 10 percent, and bottom 10 percent.
  
  
• Top-n SQL method 2 - Use the row_number function: