Retaining order in result given by SELECT with IN clause

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
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Retaining order in result given by SELECT with IN clause

Post 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)
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

You're gonna have to do this one with PHP, because it can't be done in this way with SQL.
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

Thanks -- got it.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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

Post 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
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

Sorry, I stand corrected!
Post Reply