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

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 17 Sep 2002 05:29:09 -0700
Message-ID: <6dae7e65.0209170429.4a55ea60@posting.google.com>

user00_at_hotmail.com (Steve) 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

Here is one:

SELECT username FROM (select username, rownumber() over (order by username) as rownum from users_table) as x where rownum>=500 AND rownum<=510 ORDER BY username

since I had a table with 300k rows around, I took the liberty to try it:

time db2 "select systemname from (select systemname, rownumber() over (order by systemname) as rn from phoenix.actor) as x where rn between 100000 and 100009"

SYSTEMNAME

---

0251928
0251929
0251930
0251931
0251932
0251933
0251934
0251935
0251936
0251937

  10 record(s) selected.

real    0m2.286s
user    0m0.010s
sys     0m0.020s

/Lennart Received on Tue Sep 17 2002 - 07:29:09 CDT