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

From: LLCeder <llceder_at_wxs.nl>
Date: Tue, 17 Sep 2002 02:48:14 +0200
Message-ID: <am5tbs$lb9$1@reader12.wxs.nl>

Hi Steve,

In Oracle you would use a statement like :

SELECT username FROM

                              (SELECT username
                                 FROM users_table
                               ORDER BY username)

 WHERE rownum BETWEEN 1 AND 10

But I think it's safe to say that it will not be as efficient as the LIMIT clause in MySQL.

Steve <user00_at_hotmail.com> wrote in message news:aae1fe62.0209161432.303036c3_at_posting.google.com...
> 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 Mon Sep 16 2002 - 19:48:14 CDT