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

From: BP Margolin <bpmargo_at_attglobal.net>
Date: Mon, 16 Sep 2002 19:46:44 -0400
Message-ID: <3d8670e0_1@news1.prserv.net>

Steve,

 --> SELECT TOP 500 username FROM users_table ORDER BY username

My suggestion ... specific to Microsoft SQL Server ...

First, if all you are dealing with is 500 names at a shot, consider sending them all to the front-end and do paging locally. This is probably going to give you the best performance, rather than going back and forth for every 10 names.

However, since you are ordering by username, you can also have your front-end application return to you the **last** of the username's that was just sent, and then modify your query to:

SELECT TOP 10 username
FROM users_table
WHERE username > @last_username
ORDER BY username

---

BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.

"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 - 18:46:44 CDT