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
- 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.