Limit in Oracle SQL 2

[evergrean100]

Can I use this LIMIT syntax in Oracle?

select * from TableOne LIMIT 0 , 100;

 

[SantaMufasa]

Evergrean,

Although Oracle does not have that syntax, I'm certain that we can identify a method of achieving the same results.

Please explain specifically what you want that syntax to do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

I believe it means return 100 records starting from the 1st.

We could have a debate as to how meaningless this is in relational database terms, however I can't be *rsed.

Here is what you want in Oracle terms

select * from TableOne
where rownum < 101

 

[jimirvine]

The only thing that I'd add is that you should also have an Order By clause in there for it to have any useful meaning (just as with the LIMIT clause in MySQL)

 

[Turkbear]

Hi,
and if you want other than 1 through 100 use:

Select * from ( select rownum rn,somefield1, somefield2
                from table
                order by rn )
where rn between 15 and 90

( Still relatively meaningless in a relational database table, but.. )





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[evergrean100]

Thanks,

I want the syntax to be able to do Pagination in my JSP Web page. It currently does it using the LIMIT with MySQL but now I want to do it with my JSP using Oracle 9i.

 

[SantaMufasa]

select * from TableOne LIMIT 0 , 100;

If the above code simply does a [FormFeed] every 100 rows, then here is an Oracle method of generating a [FormFeed] character (i.e., chr(12)) every 100 rows:

select t.*
       decode(mod(rownum,100),0,chr(12)||' ') " "
  from TableOne t;

Let us know if this method resolves your need (presuming that your output application recognises the [FormFeed] character.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[evergrean100]

Thanks,

I will try it on Monday when I have access to Oracle and let you know what happens.


I assume its a formfeed but not sure and I am learning alot in this post about how this is working. I assume the LIMIT command formfeeds a certain amount of records so you dont get the entire resultset at once?

Right now the JSP with MySQL that has a total of 260 records is breaking it up in the Web page showing 3 links with each page/link going to a page that shows max 100 records per page where page 1 would show records 1 - 100, page 2 would show records 101 to 200, and page 3 would show records 201 to 260.
1 2 3

 

[SantaMufasa]

The behaviour of your webpage will depend upon how it interprets the character(s) that you output in the DECODE statement, above. Oracle outputs all records at one time that comprise the result set, not just a "LIMIT's-worth" at a time.

If you want to output a specific "100" at a time, then you should use the logic that Turkbear posted, above.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[evergrean100]

My JSP Pagination now works great using Oracle SQL with many THANKS to everyone for solving my problem.
This worked for me after I used what you suggested to get MySQL LIMIT results working in Oracle. I have Java syntax in it but essentially I had to put in a > and <= instead of between syntax:

Select * from ( select rownum rn,somefield1, somefield2
                from table
                order by rn )
where rn > " + currentRs + " and rn <= " + (currentRs + recordsPerPage);

The recordsPerPage is the maximum number of records that will be shown per page and currentRs is computed like this:
currentRs = recordsPerPage * (currentPageNumber - 1)

Any thoughts or comments? Again thanks for ALL the help!

 

[SantaMufasa]

Evergrean,

I'm glad you got it working to your satisfaction, but I don't understand what the three (") expressions do/represent in your WHERE clause. Please elaborate.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

In asp, a double quote is a string delimiter and + is the concat command. The equivalent in oracle would be

where rn > '||currentRs||' and rn <. '||(currentrs + recordsperpage)

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill,

Thanks for the explanation. I guessed that might be it, but the fact that the "s were not paired threw me off. Is that legal in asp? What are they delimiting if there are only three?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

I suspect that when he cut and pasted, he cut off the quote before the select.

Bill
Oracle DBA/Developer
New York State, USA

 

[Turkbear]

Hi,
Actually the " marks are delimiting the text portions of the asp statement and concatenating the variables .
When interpreted by the VB engine it becomes a Sql statement to be sent to the database:

( assume 12 and 35 as the 2 variables)

Select * from ( select rownum rn,somefield1, somefield2
from table
order by rn )
where rn > 12 and rn <= (12 + 35)

To Paraphrase:"The Help you get is proportional to the Help you give.."