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

"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
>
> ...
>
> 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 Received on Mon Sep 16 2002 - 22:39:18 CDT