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 first published in June 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close