Page 1 of 1

Retaining order in result given by SELECT with IN clause

Posted: Wed Dec 08, 2004 12:58 pm
by voltrader
I'm using this clause to return data in an array, but I noticed that the data is returned in ascending order, instead of the order given by the IN clause.

i.e. it returns array in 1,2,3 instead of 2,3,1.

how do I get Mysql to return results in order given by the IN clause?

Code: Select all

SELECT * FROM detail WHERE ref_number IN (2,3,1)

Posted: Wed Dec 08, 2004 1:11 pm
by protokol
You're gonna have to do this one with PHP, because it can't be done in this way with SQL.

Posted: Wed Dec 08, 2004 1:15 pm
by voltrader
Thanks -- got it.

Posted: Wed Dec 08, 2004 3:55 pm
by timvw
well, you can do it.. but you whould have to introduce your own order relation..


in mysql (don't know for standard sql) you can use CASE to build the relation...

Posted: Thu Dec 09, 2004 3:46 am
by Weirdan
timvw wrote: in mysql (don't know for standard sql) you can use CASE to build the relation...
well, in MySQL much simplier way would be to use [mysql_man]field[/mysql_man] function:

Code: Select all

SELECT * FROM detail WHERE ref_number IN (2,3,1) order by field(ref_number, 2, 3, 1) asc

Posted: Thu Dec 09, 2004 7:27 am
by protokol
Sorry, I stand corrected!