Q
Problem solve Get help with specific problems with your technologies, process and projects.

Limiting number of rows returned by query

In SQL Server, there are a couple of commands (SET ROWCOUNT or TOP) that limit the number of rows that will be returned by a certain query. Is there a similar command in Oracle?

You can use ROWNUM and you can also do a TOP-n type query. Here are some examples:

If you want to return only a limited number of rows that are not in any kind of sorted order (this example returns just the first 10 selected rows):

SELECT * 
  FROM my_table
 WHERE ROWNUM < 11 ;
 
If you want to return employee records for employees with the top five salaries:
SELECT e.*
  FROM (SELECT * FROM emp ORDER BY sal) e
 WHERE ROWNUM < 5 ;
The reason you have to use the second format to retrieve the top five is that when Oracle applies a row number (rownum) to rows as they are determined to satisify the query and the order by happens after that. If you simply ask for 'where rownum < 11' as in the first example, you would pull back the rows assigned row numbers 1 - 11... not those rows whose sorted order was 1 - 11.

For More Information


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close