FYI.
(I am not in a position to implement this at the moment,
but if someone wants to, that's why I'm passing on this info.)
Oracle supports the limit natively like this
select * from prod
where rownum <= 100;
This will get the first 100 rows.
Note that rownum starts at 1 with the first row selected.
Therefore, the following will return no rows.
select * from prod
where rownum between 81 and 100;
This is because the first row is never selected, so you
never get up to 51 to begin selecting rows!
So the proper syntax for the offset+limit stuff is to
compute
maxrownum = offset + limit;
Then use the computed maxrownum
(i.e. 100, if offset = 81 and limit = 20) in the SQL
select * from prod
where rownum <= 100;
and simply throw away the first 80 rows.
Stephen
P.S. Please note that the database engine does not
guarantee that rows 61-80 will be different from
rows 81-100! This is a result of SQL's nature, that
if the result set is not ordered explicitly so that
every row has its own unique place in the order
(i.e. by a *full* primary or alternate key), its order
is not guaranteed to be the same on subsequent queries.
In practice, it usually is.
Furthermore, if rows are inserted into the database
in between the two queries, you may get unexpected results.
In practice, what is being implemented is usually adequate.
At 04:51 PM 8/8/00 -0400, you wrote:
>I just checked in the stuff for limiting the results.
>
>You use Criteria.setLimit() to limit the number of rows returned, and
>Criteria.setOffset() to change the starting row returned.
>
>If this is supported natively, the native sql to do this will be added
>to the query, otherwise, the resultset will be limited at the client.
>
>Postgres and MySQL both support limit and offset natively.
>Sybase & MS SQL/Server support limit but not offset natively.
>
>I didn't know what the deal was with the other DB's, so I didn't add
>anything for it.
>
>I only tested it with Postgres, but I put in code for Sybase and MySQL.
>Please test these.
>
> -Nissim
>
>
>------------------------------------------------------------
>To subscribe: [EMAIL PROTECTED]
>To unsubscribe: [EMAIL PROTECTED]
>Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
>Problems?: [EMAIL PROTECTED]
>
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
