Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Steve,

Here's (from memory) what DB2 offers in this area.

It's called the "FETCH FIRST n ROWS" clause.

So you would code -

SELECT username FROM users_table ORDER BY username FETCH FIRST 10 ROWS ONLY On the Linux, Unix and Windows platform you should also code "OPTIMIZE FOR 10 ROWS" to make the access path efficient. On the OS/390 (z/OS = mainframe) platform you would not need this clause (the optimizer is clever enough to work out that if you ask for 10 rows it should optimize for 10 rows <G>).

There isn't a way to get the next 10 rows as far as I know. But what you could do (and what we have done in the past) is write the code like -

SELECT username
FROM users_table
WHERE username > ? (host variable)
ORDER BY username
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS By plugging the 10th value into the host variable in the WHERE clause (and assuming that this value is unique) you can get the next ten.

I've heard that there were changing afoot to allow cursor processing in batches of x records but don't know the details.

Phil

On Mon, 16 Sep 2002 23:32:25 +0100, Steve wrote:

> I have seen this question pop up a lot. Because I think people are not
> asking properly, they are not getting the answers they are actually
> seeking.
>
> MySQL has a very nice LIMIT clause. You can pass 2 arguments to the
> LIMIT clause.
>
> --> LIMIT <beginning offset row>, <number of rows to return>
>
> So for example, in the following SQL statement,
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 0,10
>
> the usernames are sorted and the top 10 are returned. Now if you want
> the next 10 on the sorted list, use
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 10,10
>
> then the next 10 on the sorted list, use
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 20,10
>
> Note this is very nice especially when you are creating a web page where
> you want to navigate through the sorted usernames (displaying only 10
> users per page). you can hit next page and get the next 10. notice in
> this case, the only differences between the webpages, is just one number
> in the SQL statment; all the pages get 10 exact records and all you need
> to do is display them.
>
> On the 51th page, all you need to use is the following SQL statment:
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 500,10
>
> *************
>
> Now on to Oracle, MSSQL, and Others, which I know very little about.
>
> In MSSQL, people tell me to use the TOP statement. But this only can
> retrieve the top of the sorted list - I dont have the ability to choose
> the offset. So if I want to display the 51th page, I would have to use
>
> --> SELECT TOP 500 username FROM users_table ORDER BY username
>
> Which grabs 500 records! not efficient for only wanting 10 records in a
> sorted list. Imagine if there was enough records for 10,000 pages. Then
> I would have to grab many records for only wanting 10. There must be a
> better way. How are you MSSQL people doing it?? What is the most
> efficient way possible for this case? I did see somewhere else in a
> posting, you can use
>
> --> SELECT username
> FROM (SELECT TOP 10 username
> FROM (SELECT TOP 500 username
> FROM users_table
> ORDER BY username) as a1
> ORDER BY username desc) as a2
> ORDER BY username asc
>
> But damn how efficient is this? I may be wrong (depending how internally
> MSSQL is efficient), but it looks like its better to grab the records
> from the first MSSQL SQL statement.
>
> How about Oracle? How can you do it in Oracle? I know there is rownum,
> but i read somewhere
>
> --> SELECT username FROM users_table where rownum>=500 AND
> rownum<=510 ORDER BY username
>
> will not work. So what is the best way in Oracle??
>
> I am not looking for LIMIT equivalents in other Databases, because I
> know there isnt. Every database has different SQL syntax and
> functionality. But what I am asking is what is the best possible
> efficient way for the other databases to come close to match the MySQL's
> LIMIT clause. Place yourself into an administrator's shoes and ask
> yourself which way would you use? If you know, then share it here please
> (Share database name, version, examples if possible, and so on). It does
> not have to be limited to only Oracle and MSSQL, it can be about any
> other popular database that you know. And if you know any other
> databases that do have the LIMIT clause, then share it here also.
> Hopefully this will be not only a reference to me, but to anyone else
> that seeks to know this question. This scenerio comes up a lot!
>
> Thanks for your time and patience,
> Steve
Received on Tue Sep 17 2002 - 07:25:43 CDT