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

"BP Margolin" <bpmargo_at_attglobal.net> wrote in message news:3d8670e0_1_at_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

This assumes that each username hase only a limited number of occurances, in this instance a same username can be skipped if the first appears in position 10. If greater equal is used then if there are more then 10 same usernames a loop is created.

So some more information has te be added, (PK form example). Specifically for reusable (general) code this is not very suetable.

ben brugman

>
> -------------------------------------------
> 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 Wed Sep 25 2002 - 16:13:06 CDT