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, 
         Unit_Price
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, 
           Unit_Price
    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
from
(
    select distinct Product_Name as Ten_Most_Expensive_Products, 
           Unit_Price,
           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!


Copyright© GeeksEngine.com