DelphiFAQ Home Search:

How can I limit the number of rows in Oracle

 

comments29 comments. Current rating: 4 stars (18 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 So 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!!!
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?
2017-12-23, 21:58:44
Conrad from Rio De Janeiro, Brazil  
rating
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 So Paulo, Brazil  
rating
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
2019-05-29, 05:35:26
[hidden] from Chennai, India  
rating
Hello, my name is Jim and I was just looking your website delphifaq.com
over and thought I would message you on your contact form and offer
some help. I really like your site but I noticed you weren’t getting a
lot of traffic and your Alexa ranking isn’t as strong as it could be.

https://besttrafficpros.com

Fortunately, I may have an answer for you. I can get you 1,000’s of
visitors looking at delphifaq.com ready to buy your product, service or
sign up for an offer and fast. Our advertising network of over 9000
websites provides a low cost and effective online marketing solutions
that actually works. I can help your business get more online quality
traffic by advertising your business on websites that are targeted to
your specific market. The Internet is vast but you don’t have to spend
huge amounts of cash to jump start your business. I can get you 5,000
highly targeted visitors directly to your website for as little as
$29.00 for a 30 day trial run.

https://besttrafficpros.com

If you would like to talk personally and have specific questions, call
me @ 480-331-6775 from 9am to 5pm MST. Also check out the short video
here and see how everything works.

Best Regards,
James Anthony
support@besttrafficpros.com
BestTrafficpros.com
https://besttrafficpros.com
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: