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

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 - 17:32:25 CDT