Oracle select top n appearr records for each group

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 1 AND

row_num