ORDER BY NULL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

ORDER BY NULL

Post 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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: ORDER BY NULL

Post by Eran »

It's not sorted, it's just returned in the order it's found in the table.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: ORDER BY NULL

Post 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]
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

Re: ORDER BY NULL

Post 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
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

Re: ORDER BY NULL

Post 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 ?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: ORDER BY NULL

Post 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.
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

Re: ORDER BY NULL

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: ORDER BY NULL

Post 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() ?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: ORDER BY NULL

Post 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, ...)
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

Re: ORDER BY NULL

Post 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
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

Re: ORDER BY NULL

Post by cardi777 »

yup works beautifully.

is the ORDER BY FIELD feature unique to mysql ?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: ORDER BY NULL

Post 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.
Post Reply