Page 1 of 1

ORDER BY NULL

Posted: Wed Jun 23, 2010 10:50 am
by cardi777
I am trying to list a bunch of rows in the sequence that I am asking for them:

Code: Select all

WHERE ID = 20 OR ID = 2 OR ID = 4 OR ID = 5 OR ID = 23 OR ID = 14 OR ID = 15 OR ID = 16 OR ID = 28 OR ID = 37 OR ID = 25 OR ID = 22 OR ID = 21  ORDER BY NULL 
but, alas, it gets sorted smallest number to largest... suggestions to make it stop sorting it? help!

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 10:57 am
by Eran
It's not sorted, it's just returned in the order it's found in the table.

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 3:16 pm
by John Cartwright
Just so you know, your expression can be simplified using the IN() operator.

[text]WHERE ID = 20 OR ID = 2 OR ID = 4 OR ID = 5 OR ID = 23 OR ID = 14 OR ID = 15 OR ID = 16 OR ID = 28 OR ID = 37 OR ID = 25 OR ID = 22 OR ID = 21[/text]

[text]WHERE id IN(20,2,4,5,23,...)[/text]

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 6:19 pm
by cardi777
mmm i didn't know about the IN() technique! Thanks

Does using IN mean that the array will be ordered in the fashion it was fetched? OR is there no way of doing this

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 6:20 pm
by cardi777
pytrin wrote:It's not sorted, it's just returned in the order it's found in the table.
mm that would make sense... is there a way around it though ?

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 6:52 pm
by Eran
You can either have it sorted by some criteria or it will be returned by the order it's found in the table. The results have to appear in some order, even if it's the actual order of the rows in the database.

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 7:08 pm
by cardi777
how on earth did I get myself into a situation where I need this particular thing... geez :P

Maybe I just need to write a script that will reorder the multi dimensional array from the db query

Re: ORDER BY NULL

Posted: Wed Jun 23, 2010 7:10 pm
by Eran
maybe if you explained what's the problem with the order of the rows, someone would suggest an appropriate solution. Perhaps what you are looking for is ORDER BY RAND() ?

Re: ORDER BY NULL

Posted: Thu Jun 24, 2010 5:25 am
by Weirdan
pytrin wrote:Perhaps what you are looking for is ORDER BY RAND() ?
Most likely he is looking for ORDER BY FIELD(ID, 20, 2, 4, ...)

Re: ORDER BY NULL

Posted: Thu Jun 24, 2010 5:33 am
by cardi777
Weirdan wrote:
pytrin wrote:Perhaps what you are looking for is ORDER BY RAND() ?
Most likely he is looking for ORDER BY FIELD(ID, 20, 2, 4, ...)
thats it! you solved it

never heard of ORDER BY FIELD before, but I googled it and I think its a winner.

thx sooo much

Re: ORDER BY NULL

Posted: Thu Jun 24, 2010 8:45 am
by cardi777
yup works beautifully.

is the ORDER BY FIELD feature unique to mysql ?

Re: ORDER BY NULL

Posted: Thu Jun 24, 2010 9:43 am
by Weirdan
cardi777 wrote:is the ORDER BY FIELD feature unique to mysql ?
field() is built-in function that seems to be unique to mysql, yes. You could convert it into CASE ID WHEN 20 THEN 1 WHEN 2 THEN 2 WHEN 4 THAN 3 [....] ELSE 0 which would be more portable.