Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement 
Author Message
 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



Sat, 05 Mar 2005 06:32:25 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement



Quote:
> [ how to efficiently browse in an RDBMS ]

Hey Steve,

I've been meaning to write a white paper on this very topic.  I'll
give you a 50,000' view... :)

The idea is to create a '[pad]' table which houses a list of 'id' (I
use surrogate ID's in all my tables!  <G>)  The [pad] table is a
recursive table which allows us to have as many rows as needed to
store the ID's that we need:

  /* avoid fragmentation by using CHAR's.  Map the size of each row
     to create even multiples of rows per data page */
  [pad]
     id           NUMERIC(16)
     scratch_1    CHAR(...)
     scratch_2    CHAR(...)
     parent_id    NUMERIC(16)

When we wish to browse through a list of rows across many different
tables, we generate a list of surrogate ID's off of the main
'driver' table.  Using Oracle's bulk-collects, we can efficient
snarf, say 400 id's -- after all, who wants to browse through more
than 400 rows??!  <g>

We create an entry or entries in the [pad] table:

   233, 444, 2333

We return to the front-end the first page of rows _and_ the [pad].id
value.

If the front-end wishes to view the next page, they send the stored
procedure the [pad].id and the row ranges to view.

The underlying packages fetch from the [pad] table the list of ID's
and use dynamic SQL (Oracle and Sybase ASE support this) to retrieve
that set of data:

     /* Use an ORDER BY to reflect the orignal set of data */
     select .... from .... where ... a.id in ('233, 444, 2333')
     order by ...

HTH!  :)

ps:  My plan was to really write this up by Oct 1st:
     http://www.hpdbe.com/white_papers/index.html
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com



Sat, 05 Mar 2005 06:46:35 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
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

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.


Quote:
> 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



Sat, 05 Mar 2005 07:46:44 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

I have some doubts about the wisdom of the approach represented by these
MySQL select statements. It seems to me that each one is a separate query,
unrelated to the previous or next query, and therefore the result set seen
by the user could be corrupted by the occurrence of inserts and deletes
committed in the users_table while this page-by-page browsing is ongoing.
(Unless the equivalent of SET TRANSACTION READ ONLY is in effect to ensure
that multiple queries have the same read-consistent data view).

The approach I would take in Oracle is to open a cursor with my complete
query (select username from users_table order by username), then fetch
rows from the cursor 10 at a time. The view of the data seen by the user
would be read-consistent with the moment in time that the cursor was
opened (that is, the effect of any inserts or deletes by other sessions
would not be visible to the user browsing the list). My program logic
might include a message to the user on each page to reveal the defining
moment ("Information current as at 9/16/02 17:01 pm") and a timer to close
the cursor if no browsing activity detected within the threshold period.
Since Oracle does not block readers or writers, holding the query open is
not preventing access to the users_table for concurrent reading and
writing.

Regarding ROWNUM, the numeric values of this pseudo-column are assigned to
each result row before sorting, but you can work around this by using an
inline view, viz:

select *
from (select username from users_table order by username) u
where u.rownum between 501  and 510

Note: the ability to include ORDER BY in a subquery was introduced in
Oracle8i (not sure which exact version)

Martin Doherty

Quote:

> 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

  martin.doherty.vcf
< 1K Download


Sat, 05 Mar 2005 08:25:10 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
Hi Steve,

In Oracle you would use a statement like :

SELECT username FROM
                              (SELECT username
                                 FROM users_table
                               ORDER BY username)
 WHERE  rownum BETWEEN 1 AND 10

But I think it's safe to say that it will not be as efficient as the LIMIT
clause in MySQL.


Quote:
> 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



Sat, 05 Mar 2005 08:48:14 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Quote:

> In Oracle you would use a statement like :

> SELECT username FROM
>                               (SELECT username
>                                  FROM users_table
>                                ORDER BY username)
>  WHERE  rownum BETWEEN 1 AND 10

> But I think it's safe to say that it will not be as efficient as the LIMIT
> clause in MySQL.

Why? I'd imagine they look very much the same internally.

Marshall



Sat, 05 Mar 2005 11:36:33 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Quote:

> 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

> ...

> In MSSQL, people tell me to use the TOP statement. But ...
> ... 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.

What makes you think this is going to be any different from how the
MySQL version works? What you are seeing is pretty much just
two different ways of expressing the same idea. One might be faster
or the other might be, or they might be about the same, but how can
you tell by looking at the SQL statement? You can't.

Marshall



Sat, 05 Mar 2005 11:39:18 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
Hi,

Isn't this a thing that maybe the web-server should take care of? Zope,
for instance, handles it that way: the query is issued once to the
database for the first page, retrieving the result set and caching it
(there is an upper limit of rows retrieved which can be set to a
sufficiently high number). Now for subsequet pages the cached values are
used. Seems to be rather elegant as it's a solution not dependent on a
specific database.

regards,
stephan

Quote:

> 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



Sat, 05 Mar 2005 16:27:56 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
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

Quote:

> 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



Sat, 05 Mar 2005 20:25:43 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Quote:

> 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



Sat, 05 Mar 2005 20:29:09 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
No.  Usually to scale the web server is stateless.  Otherwise what do you do
when someone leaves?  You can't tell so you have cached this set into memory
and you have to wait x number of minutes fot it to time out.  In the
meantime you are wasting this ram.  Now multiply by the huge number of
connections and you are eating up a lot of memory.
Jim

Quote:
> Hi,

> Isn't this a thing that maybe the web-server should take care of? Zope,
> for instance, handles it that way: the query is issued once to the
> database for the first page, retrieving the result set and caching it
> (there is an upper limit of rows retrieved which can be set to a
> sufficiently high number). Now for subsequet pages the cached values are
> used. Seems to be rather elegant as it's a solution not dependent on a
> specific database.

> regards,
> stephan


> > 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



Sat, 05 Mar 2005 21:16:13 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
For those of you looking to simulate a MySQL limit statement, try this
article:

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/31/pid/333/qid/32...

Why nobody other than the folks at MySQL have implemented a 'limit'
clause (it's in the ANSI standard) is beyond me. I expect Microsoft to
behave like this but not Oracle.



Sun, 06 Mar 2005 07:09:52 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Quote:

> For those of you looking to simulate a MySQL limit statement, try this
> article:

> http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/31/pid/333/qid/32...

> Why nobody other than the folks at MySQL have implemented a 'limit'
> clause (it's in the ANSI standard) is beyond me.

Which ANSI standard? Definitly not SQL ;-)
There's no LIMIT clause up to the new drafts for SQL:200x, LIMIT's not even
a keyword.

And it will probably never be a part of standard SQL, because you can
achieve the same using ROW_NUMBER.

Dieter



Sun, 06 Mar 2005 15:20:34 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
crackhitler wrote on Wednesday 18 September 2002 01:09:

Quote:
> Why nobody other than the folks at MySQL have implemented a 'limit'
> clause (it's in the ANSI standard) is beyond me.

I am a bit curious...  Where did you find it in the SQL standard?

--
Knut Stolze
DB2 Spatial Extender
IBM Germany / University of Jena



Sun, 06 Mar 2005 15:49:58 GMT
 Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Quote:

> 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

It's also possible to fetch any "window", rather than just the first n
rows, using an OLAP function, e.g.:

SELECT
  a.username
FROM
  (
    SELECT
      ROW_NUMBER() OVER (ORDER BY username) AS row_number,
      username
    FROM
      users_table
  ) AS a
WHERE
  a.row_number BETWEEN 11 AND 20;

Jeremy Rickard



Sun, 06 Mar 2005 19:07:57 GMT
 
 [ 25 post ]  Go to page: [1] [2]

 Relevant Pages 

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

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

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

4. MySQL vs Oracle vs MSSQL Vs others

5. PostgreSQL vs Oracle vs DB2 vs MySQL - Which

6. PostgreSQL vs Oracle vs DB2 vs MySQL - Which sh

7. PostgreSQL vs Oracle vs DB2 vs MySQL - Which should

8. PostgreSQL vs Oracle vs DB2 vs MySQL - Which should

9. Oracle vs PostgreSQL vs MySQL vs mSQL : COMPARISON

10. TOP of MSSql vs. limit of MySql

11. SQL differences: Oracle vs Sybase vs others

12. Need Information about Oracle Market Share (vs DB2 vs MSSQL)


 
Powered by phpBB® Forum Software