DelphiFAQ Home Search:

How can I limit the number of rows in Oracle


comments29 comments. Current rating: 4 stars (17 votes). Leave comments and/ or rate it.


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?


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;

Content-type: text/html


You are on page 2 of 2, other pages: 1 [2]
2009-12-08, 09:05:21   (updated: 2009-12-08, 09:46:13)
Bernhard from Germany  
It depends what you want. I use this:

( selectquery )
WHERE rownum <= 30

with 'selectquery' being my query, as e.g.

(SELECT t.field1, t.field2 FROM table t
ORDER BY t.field2 DESC)
where rownum <= 30

because I want the result's top-30, not run a query on the initial table's top 30.

2010-04-16, 13:36:53
anonymous from São Paulo, Brazil  
Using CTE (Commom Table Expression)

            with pagination as
                select row_number() over
                    (order by idEmployee) as rowNo,
                    idEmployee, Name, LastName, Age, Gender
            from pagination
                rowNo between @actualRecord and @rowsPerPage

2010-04-23, 18:43:14
anonymous from Mexico  
2011-11-09, 07:50:05
anonymous from India  

I am having an oracle table which is having 8 columns. I need to find out the duplicate records based on the conbination of all 8 columns.

Can any body suggest ?
2012-02-15, 09:55:20
anonymous from United States  
2012-04-10, 18:47:08
crowdplz from United States  
2012-06-04, 04:21:27 from Ethiopia  

2012-08-08, 12:42:02
anonymous from Canada  
select * from table where etime = (select max(etime) from dblogs where ip = 'x.x.x.x') and ip = 'x.x.x.x';

rownum failed hard for what I was doing, as i am looking for the previous incident to a specific time. This query works well, with a subquery... although the performance makes me miss mysql 'LIMIT 1'
2012-09-21, 19:46:36
anonymous from Sweden  
2012-11-16, 11:36:20
Rodolfo C from Monterrey, Mexico  
Muchas gracias!!!
2012-12-19, 00:08:12
[url= http://www.wonderfu..tml]ibeats earphones[/url] is characterized by its solid housing,sllyjfdibeats balanced sound quality and purest sound producing. Monster Ibeats have Solid metal housing resists vibrations for purest sound without sonic side effects ControlTalk on-cable mic for convenient hands-free calling with iPhones and smartphones ControlTalk track seeking capabilities without touching your iPod or music phone. This [url= http://www.wonderfu..tml]ibeats earphones[/url] unlike ordinary in-ear headphones,ibeats earphones are constructed from solid metal housing.With the [url= http://www.hotmusic..html]beats by dre mixr[/url], you can freely enjoy the beautiful music without being disturbed by the outside noise.We supply all kinds of beautiful Monster [url=]Beats by dre[/url], such as ibeats earphones,[url= http://www.wonderfu...html]solo hd bluetooth[/url],[url=]beats studio headphones[/url] and [url=]purple beats by dre[/url]
2017-04-15, 01:42:48
Josefina from France  
Hey! Do you know if they make any plugins to protect against hackers?
I'm kinda paranoid about losing everything I've worked hard on.
Any suggestions?
2017-12-23, 21:58:44
Conrad from Rio De Janeiro, Brazil  
I have noticed you don't monetize your site, don't waste your traffic,
you can earn extra cash every month because you've got hi quality content.
If you want to know how to make extra $$$, search for: Boorfe's
tips best adsense alternative
2018-01-21, 13:48:42
Jacques from São Paulo, Brazil  
Hi blogger, i must say you have high quality posts here.
Your page can go viral. You need initial traffic only.
How to get it? Search for: make your content go viral Wrastain's tools
You are on page 2 of 2, other pages: 1 [2]



NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: