Q

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


This was last published in June 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close