| > Does that even make sense? "records 30 - 45" has no meaning, given
| > that the server is free to return records in any order it pleases.
|
| a select query issued on a table with an autoincremented field and no
ORDER
| clause seems to default to having it's results returned in order of
the
| autoincremented field, ASC. I was saying "records 30 - 45" in terms of
| that.
[First LIMIT 30,45 will return 45 records (not 15) starting at 30. Check
the manual.]
[Second, NEVER assume that records are returned in some order based on
autofield. The better assumption would be that if it happens to be true,
that MySQL will also optimize out the ORDER BY autofield.]
Tell it to do what you want. The real dilema is that you want two
queries really. You want the records ordered so you can choose a subset,
then you want that subset ordered.
You have choices:
0. <<NOT A CHOICE WITH MYSQL>> Ideally you likely want a subquery.
Something like:
select * from mytable
order by somefield ASC
where autofield in
(select autofield from mytable
order by autofield ASC
limit 30,15);
1. You can use a temp table to get around this :
create temporary table submytable
select * from mytable
order by autofield ASC
limit 30,15;
select * from submytable
order by somefield ASC;
If the data in the table rows is too big, you can be more specific and
do what MySQL would likely do itself if it supported subqueries:
create temporary table submytable
select autofield from mytable
order by autofield ASC
limit 30,15;
select mytable.* from mytable, submytable
where mytable.autofield = submytable.autofield
order by somefield ASC;
2. Have the client do the second sort:
select * from mytable
order by autofield ASC
limit 30,15;
Then the client of MySQL can do its own sorting. For 15 fields, this
would likely be the best bet and would scale better, since clients
generally scale better than databases.
3. << MAKES ASSUMPTIONS>> If you know that there are never any holes
(deleted records, or skips in the numbering of the records in autofield)
then just use a where:
select * from mytable
where autofield between 30 and 45
order by somefield ASC;
Sincerely,
Steven Roussey
http://Network54.com/?pp=e
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
