Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: