DelphiFAQ Home Search:
General :: Databases :: Oracle
Basic information to help you getting up to speed with Oracle, and solutions for advanced problems.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

How can I limit the number of rows in Oracle

Question:

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?

Answer:

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;

Generated 16:00:30 on Jan 23, 2017