Select Top n Rows in Oracle vs Transact-SQL

by Andy
Posted on January 20, 2018July 29, 2020

Select Top n Rows in Oracle (Retrieve only the top n rows in Oracle)

[AdSense-A]

From time to time you need to return the top n rows in Oracle from a table. For example “I need the top X customers from the sales table”. I know how to do this in transact-SQL but how do I do this in PL/SQL? This is pretty trivial but for someone who goes back and forth between Tansact-SQL and Oracle Pl/SQL I just wanted a place to remember the differences. Hopefully these examples of select top n rows in Oracle will help you as well.

In T-SQL you would simple use the TOP “X” keyword similar to the following example:

SELECT TOP 10 CUSTOMER_ID 
FROM CUSTOMER 
ORDER BY CUSTOMER_ID

SELECT TOP 10 CUSTOMER_ID

FROM CUSTOMER

ORDER BY CUSTOMER_ID

In Oracle the ‘TOP’ keyword doesn’t exist. You can achieve the same effect using the following example:

SELECT CUSTOMER_ID
FROM PS_CUSTOMER
ORDER BY CUSTOMER_ID
WHERE ROWNUM <= 10

SELECT CUSTOMER_ID

FROM PS_CUSTOMER

ORDER BY CUSTOMER_ID

WHERE ROWNUM <= 10

Need more rows? Just increase the 10 to whatever count needed in either language. This return can then be joined to other tables to create a full report. This is a trivial simple example but you get the point how to retrieve top n rows in Oracle. That is it. Pretty simple, right?

Check out other SQL help on my blog in the SQL category.