Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to use a "LIMIT" with Oracle 8i
- Thread starter HoMyGod
- Start date
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
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
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:
It appears that limit 11,20 means "select 20 rows starting with offset 11". 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
It appears that limit 11,20 means "select 20 rows starting with offset 11". 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
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.
Mike.
- Status