[RESOLVED] Order by in query dropping results

elijahg

Hi,

I have a rather basic query in the form of

select * from table where column in (1,2,3,4) order by column asc

The query for examples sake would return rows 1,2, and 3 and not return 4. Yet if I take out the order by clause then it returns all the results.

Does anyone know what could be causing that?

Thanks.

tomhath

I doubt the ORDER BY is changing the number of rows. Are you running it by itself or as part of a bigger query or php script? The ORDER BY must have revealed a bug that's someplace else in your program.

elijahg

There isn't a whole lot going on before the query is run that could cause it. I've verified the variables step by step and they all check out properly. Doing a mysql_num_rows with the order by there shows the correct amount of rows, but putting a counter in on the while( $var = mysql_fetch_array loop shows that it's not looping through all the results. And as said before removing the order by from the query then gives all the expected results.

dagon

do you get the same re results trying it in something like mysqladmin?

elijahg

Ok, it's been resolved. I feel a bit stupid for not noticing a nested query not set to a different variable after having looked over the thing so many times. The reason the order by was causing issues was with it not being ordered the row that was triggering the nested query was coming at the end. Anways, thanks.