DelphiFAQ Home Search:

How can I limit the number of rows in Oracle

 

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

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;

Content-type: text/html

Comments:

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  
rating
It depends what you want. I use this:

SELECT * FROM
( selectquery )
WHERE rownum <= 30

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

SELECT * FROM
(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.

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


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


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

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  
rating
2012-04-10, 18:47:08
crowdplz from United States  
2012-06-04, 04:21:27
warsenal3@gmail.com from Ethiopia  
rating
jjjjjjjjjjjj'


Keywords:
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  
rating
2012-11-16, 11:36:20
Rodolfo C from Monterrey, Mexico  
rating
Muchas gracias!!!
2012-12-19, 00:08:12
anonymous  
[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= http://www.wonderfu..com/]Beats by dre[/url], such as ibeats earphones,[url= http://www.wonderfu...html]solo hd bluetooth[/url],[url= http://www.hotmusic..com/]beats studio headphones[/url] and [url= http://www.hotmusic..om/]purple beats by dre[/url]
2017-04-15, 01:42:48
Josefina from France  
rating
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?
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: