Ano-Tech Computers
Enter keyword:

Oracle SQL: How limit the number of rows in a query
Problem:
I have got a query that returns thousands of rows but I'm only interested in the first 20 records. In mysql I can limit the returned data (and thus the network traffic) with the LIMIT start,number clause, where start is the starting row and number is the number of rows that I want to see. Is something similar possible in Oracle as well?
 
Solution:
Oracle has a system attribute ROWNUM for each record returned. A query that only returns the first 20 records would look like the one in the example.

ROWNUM starts counting with 1.

// mysql
select col from tbl limit 20;

// Oracle
select col from tbl where rownum<=20;

// Microsoft SQL
select top 20 col from tbl;

Source: http://www.delphifaq.com/faq/databases/oracle/f594.shtml
 
Discuss this solution
Did this article solve your problem? Yes No Did not apply

This is free technical advice without any warranties. Use at your own risk. Solutions offered may not apply to your particular problem at all. Opinions and views expressed in the articles are the authors' personal and may not necessarily reflect or coincide with those of Ano-Tech Computers. We take no responsibility if anything bad happens from following any of the intructions given. If you don't understand and accept this, please contact us for personal assistance instead.
To read our full disclaimer, go here.

We welcome anyone who is willing to contribute to this public knowledge base, contact siteadmin@atc.no if you have information you would like to share. The idea is not to replace the commercial support sites, but to publish those hard-to-find solutions you've found yourself looking for over and over again.

Show all articles