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

From: Blair Kenneth Adamache <badamache_at_yahootoomuchspam.com>
Date: Tue, 17 Sep 2002 09:51:01 -0400
Message-ID: <3D873345.AFC6015F@yahootoomuchspam.com>

DB2 also supports IDENTITY columns and has a rownumber function:

SELECT * FROM
    (SELECT name, rownumber() over

            (order by name)
        AS rn FROM address)

    AS tr WHERE rn between 10 and 20

Philip Nelson wrote:

> Steve,
>
> Here's (from memory) what DB2 offers in this area.
>
> It's called the "FETCH FIRST n ROWS" clause.
>
> So you would code -
>
> SELECT username FROM users_table ORDER BY username FETCH FIRST 10 ROWS
> ONLY
>
> On the Linux, Unix and Windows platform you should also code "OPTIMIZE
> FOR 10 ROWS" to make the access path efficient. On the OS/390 (z/OS =
> mainframe) platform you would not need this clause (the optimizer is
> clever enough to work out that if you ask for 10 rows it should optimize
> for 10 rows <G>).
>
> There isn't a way to get the next 10 rows as far as I know. But what you
> could do (and what we have done in the past) is write the code like -
>
> SELECT username
> FROM users_table
> WHERE username > ? (host variable)
> ORDER BY username
> FETCH FIRST 10 ROWS ONLY
> OPTIMIZE FOR 10 ROWS
>
> By plugging the 10th value into the host variable in the WHERE clause
> (and assuming that this value is unique) you can get the next ten.
>
> I've heard that there were changing afoot to allow cursor processing in
> batches of x records but don't know the details.
>
> Phil
>
> On Mon, 16 Sep 2002 23:32:25 +0100, Steve wrote:
>
> > 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 Tue Sep 17 2002 - 08:51:01 CDT