DelphiFAQ Home Search:

How can I limit the number of rows in Oracle

 

comments30 comments. Current rating: 4 stars (19 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-09-21, 19:46:36
anonymous from Sweden  
rating
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: