Re: Need some help with ROWNUM

Can you help me out a little bit with MySQL? The reason we are doing all the sorting is because the row order does not have any significance in and of itself. What is the "need", for lack of a better term, of getting rows 100,000 through 101,000? What is significant about them? In Oracle, the rows can come out in any order, usually in the order they are placed in the table in the first place, but that has no business significance usually. When you say you can do this easily with MySQL - what is it doing? Is is pre-ordered by some criteria that you are interested in? What makes rows 100,000 - through 101,000 any different from 90,000 to 91,000?

On Wed, 7 Feb 2001 09:54:55 -0000, "Stephen Livesey" <ste_at_exact3ex.co.uk> wrote:

>I am having a very similar problem to yours, and your solution works.
>
>However I am finding it takes quite a while to run.
>
>I am running it on a database with 2 million records, and I am just trying
>to select say records 100000 to 1001000.
>
>I believe it takes a long time to run because of the 'sorting', even though
>I am using a primary key.
>
>Is there any way of performing the query without having to sort the data?
>I have tried removing the 'order by' commands, but then it does not return
>the correct results.
>
>
>I can perform the same query using MySQL in a fraction of the time using:
> select * from table limit 100000,1000
>
>
>Thanks
>Stephen Livesey
>
>
>"Gad Krosner" <krosner_at_excite.com> wrote in message
>news:95ovfj$1sa$1_at_nnrp1.deja.com...
>> That did (mostly) the trick - thanks. It needs a third sort. Also,
>> when going through the last set of results, the 26 needs to be computed
>> such that it shows only the remaining rows for the last page. Here's
>> the solution (not showing computation for last page - done in the app):
>>
>> SELECT * FROM (
>> SELECT * FROM (
>> SELECT * FROM (
>> SELECT columns
>> FROM tables
>> WHERE join conditions, including outer join
>> ORDER BY some columns)
>> WHERE ROWNUM < 1026
>> ORDER BY some columns DESC)
>> WHERE ROWNUM < 26)
>> ORDER BY some columns
>>
>>
>>
>> In article <sntu7t022qo8dvslpufuis7uv3jsjd5tf8_at_4ax.com>,
>> Doug C <dcowles_at_i84.net> wrote:
>> > Rather than running it twice, how about running it once with
>> > select * from
>> > (
>> > select * from
>> > ((
>> > SELECT columns
>> > FROM tables
>> > WHERE join conditions, including outer join
>> > ORDER BY some columns
>> > )
>> > WHERE ROWNUM < 1026
>> > )
>> > ORDER IT BACKWARDS
>> > )
>> > WHERE ROWNUM < 26
>> >
>> > Not sure if I have all the matching parenthesis etc.,
>> >
>> > On Mon, 05 Feb 2001 18:02:52 GMT, Gad Krosner <krosner_at_excite.com>
 wrote:
>> >
>> > >Thanks, Steve. I tried that route and it didn't work quite as
 expected.
>> > >
>> > >For one thing, it didn't return exactly 25 rows. I got either 21 for
>> > >rownum in the range 1001 and 1026, or 27 for rownum between 2001 and
>> > >2026.
>> > >It also seems to run twice as long because these queries run
>> > >independently.
>> > >
>> > >
>> > >In article <t7to4v6mlocv5a_at_corp.supernews.co.uk>,
>> > > "Steve Blomeley" <steveblomeleyATyahoo.co.uk> wrote:
>> > >> Gad,
>> > >>
>> > >> How about ...
>> > >>
>> > >> SELECT * FROM
>> > >> (
>> > >> SELECT columns
>> > >> FROM tables
>> > >> WHERE join conditions, including outer join
>> > >> ORDER BY some columns
>> > >> )
>> > >> WHERE ROWNUM < 1026
>> > >> #
>> > >> MINUS
>> > >> #
>> > >> SELECT * FROM
>> > >> (
>> > >> SELECT columns
>> > >> FROM tables
>> > >> WHERE join conditions, including outer join
>> > >> ORDER BY some columns
>> > >> )
>> > >> WHERE ROWNUM < 1001
>> > >>
>> > >> hth
>> > >> Steve Blomeley
>> > >>
>> > >>
>> > >
>> > >
>> > >Sent via Deja.com
>> > >http://www.deja.com/
>> >
>> >
>>
>>
>> Sent via Deja.com
>> http://www.deja.com/
>
Received on Thu Feb 08 2001 - 00:39:58 CST