In SQL Server, there are a couple of commands (SET ROWCOUNT or TOP) that limit the number of rows that will be...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.