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:
- 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:
select
*
from
(select empno, sal row_number()
over
(order by sal desc) rnk from emp)
where rnk <= 5;
