Custom Search

How to get Top N rows from Oracle by using SQL

To get top N records from Oracle is tricky and there are basically three different ways (based on the Oracle versions) to get them by using Oracle SQL.

Below we use the Products table in Oracle Northwind datatbase to demonstrate how to get top 10 most expensive products.

The three queries below return the same result. It demonstrates how Oracle limits the number of records selected. The top-N query in Oracle is not as easy to create as those in MS SQL Server (TOP n keyword) or MySQL (LIMIT keyword).

Query 1 - works on all Oracle versions

The first query uses correlated sub-query to get the top 10 most expensive products. It works for any Oracle versions, including Oracle 8i or older.

select distinct Product_Name as Ten_Most_Expensive_Products, 
from Products a
where 10 >= (select count (distinct Unit_Price)
                    from Products b
                    where b.Unit_Price >= a.Unit_Price)
order by Unit_Price desc;

Query 2 - works on Oracle 8i and above

The second query retrieves data from an ordered sub-query table. ROWNUM pseudo-column is used outside the sub-query to restrict the number of rows returned. For Oracle 8i and above, we can use this fashion to get the Top N rows by using a sub-query with ORDER BY clause and rownum function in outer query.

select * from
    select distinct Product_Name as Ten_Most_Expensive_Products, 
    from Products
    order by Unit_Price desc
where rownum <= 10;

Query 3 - works on Oracle 9i and above

The third query uses analytic function ROW_NUMBER() to get ranks with OVER function. From Oracle 9i and above, the RANK function (as well as DENSE_RANK) can be used to get the TOP N records.

select Ten_Most_Expensive_Products, Unit_Price
    select distinct Product_Name as Ten_Most_Expensive_Products, 
           ROW_NUMBER() over (order by Unit_Price desc) row_number
    from Products
    order by Unit_Price desc
where row_number between 1 and 10;

Here is the query result. 10 records returned.

Happy Coding!


Other Recent Articles from the Database SQL category:

1.Get single records when duplicate records exist
2.Find duplicate values or non-duplicate values in a table
3.How to get Top 1 record from Oracle by using PL/SQL
4.How the data types in Access Northwind are converted to Oracle
5.How to do cross table update in Oracle
6.Export Northwind Access database to MySQL via ODBC

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy