order by, limit only ordering selected rows in mysql 3.23.57

thehedgehog

i've never had this happen before, so i'm highly confused...did something change in the way mysql orders results when using limit? my webhost is running 4.0.18, but i've recently been developing locally using 3.23.57.

(edit: this can't be a difference in versioning, as i've now just upgraded to mySql 4.1 and am experiencing the same behavior.)

in a usertable showing all users by ID, i have my testing accounts:

hedgehog
administrator
god
visitor
beth
fooli
user
test
mitch
peter

an example query:

$connection->queryString("SELECT * FROM $usertable ORDER BY username ASC LIMIT 0,6");

this returns:

hedgehog
god
fooli
beth
administrator.

i expect it to return:

[edit: updated expected return after sanam pointed out i'd typed the wrong order for that sort 🙂]
administrator
beth
fooli
god
hedgehog

sorting by my last_logged and member_since fields, it seems apparent that mysql is processing only the limited selection when ordering its results.

i've never experienced this behavior before, it's always ordered the table then extracted a limited selection based on what's returned.

sanam

USE "DESC" instead of "ASC" if you want records starting from z instaed of a...

thehedgehog

desc gives me:

mitch
peter
test
user
visitor

i'd expect

visitor
user
test
peter
mitch

(and obviously if paginated, the rest of them would proceed in that way, all the way down to administrator)

where it gets really obvious that something's fecked is when i sort it by last_logged. there's some users that have never logged in (ie: the timestamp is null) showing up with users that have a timestamp. it's fairly obvious that it's just picking the first few rows, then sorting what it's got based on the criteria. the sorting is proper, but only for the limited result set, not the whole table.

it should also be noted the second i remove the LIMIT, it sorts exactly as expected.

sanam

It would be much help if you put the whole query in here, add the rest of the sorting fields also.

I did ran few queries on my db and results where fine.
ON Desc:

SELECT Name
FROM country
ORDER BY Name DESC LIMIT 0 , 5

Name
Zimbabwe
Zambia
Yugoslavia
Yemen
Western Sahara

SELECT Name
FROM country
ORDER BY Name DESC LIMIT 5 , 5

Name
Wallis and Futuna
Virgin Islands, U.S.
Virgin Islands, British
Vietnam
Venezuela

thehedgehog

<moron alert> heh, my post trigger finger is a bit itchy apparently...

$c = count($list_ID);
$row_count = $c;
$c--;
while($c>=0) {

when doing a reverse count to get the values, it's prolly a good idea to um... yeah.

</moron alert>

sanam

in that case you should try php reverse array function. check this link might be of more help.

http://ca2.php.net/function.array-reverse