How to use a "LIMIT" with Oracle 8i

[HoMyGod]

Hi,

I am trying to convert mySQL request into Oracle one.
I have something like this :

select * from bmp_rtt limit 0,10

or another one:

select * from bmp_rtt limit 11,20

Is it possible to do that easily in Oracle SQL? Otherwise, what can be a good idea?

Thank U

 

[LokiDba]

Hi,

If limit is used to reduce the number of rows returned then the first query can be done as follows in Oracle:

SELECT * FROM bmp_rtt
WHERE rownum < 11

ROWNUM is the count of the number of rows returned by the query. You cannot do the second query using rownum because as soon as the condition which the ROWNUM in is false it stops the query.

The way to do it for the second query is as follows:

SELECT ...all the col names required
FROM ( SELECT rownum counter, ...all the col names required
FROM bmp_rtt WHERE rownum < 21)
WHERE counter BETWEEN 11 AND 20

This is using an inline view to add the counter column on to your table and you then use that column in the where clause of the original statement.

Hope this is of some use

LokiDBA

 

[karluk]

LokiDBA is right about the Oracle syntax, but I think he is misinterpreting the original mysql statements. (This is based not on my personal knowledge of mysql, which is zero, but on the following link:

http://www.bitmechanic.com/mail-archives/mysql/current/0892.html.)

It appears that limit 11,20 means &quot;select 20 rows starting with offset 11&quot;. Also offsets in mysql start with 0 unlike Oracle rownum, which starts with 1. Therefore selecting with limit 11,20 translates to selecting Oracle rows with rownum 12 through 31. The resulting query is

SELECT ...all the col names required
FROM ( SELECT rownum counter, ...all the col names required
FROM bmp_rtt WHERE rownum < 32)
WHERE counter BETWEEN 12 AND 31

 

[MikeJones]

I had to do a similar thing myself of late (Using rownum in the subquery that is) and due to the fact that Oracle does not garentee the order that rows are returned from a table you can find that running the query twice gets different reuslts, (This is what happened to me). For that reason alone I'd suggest putting a order by on the inner query.

Mike.