Actually I don’t think it needs a guru, my problem seems pretty trivial.
I was asked to provide another ordering option for the content item module so that it would display articles in the order of the ids entered in the module settings. The query looks something like
SELECT [somestuff] FROM [thetable] WHERE id = 5 OR id = 3 OR id = 7 [somejoinsandconditions]
I want the results in the order 5-3-7 but I get of course 3-5-7 and I simply don’t know how to write an orderclause that would return the desired result.
Anyone ?
Howdy Eike.
You can always run 3 queries in the desired order, one for each ID, then merge the results into an array. Makes sense?
Comment by Manolo — February 1, 2010 @ February 1, 2010 6:33 am
Sorry, didn’t check enough, copied from dev.mysql.com, http://dev.mysql.com/doc/refman/5.0/en/select.html:
If you want to get the record in an specific order you can do it like this
SELECT * FROM table ORDER BY FIELD( id, 23, 234, 543, 23 )
id | name
+++++++++++++++++++++++++++++++++++++++++++++++++
23 | rene
234 | miguel
543 | ana
23 | tlaxcala
or if the table as a name
SELECT * FROM table ORDER BY FIELD( name, ‘miguel’, ‘rene’, ‘ana’, ‘tlaxcala’ )
id | name
++++++++++++++++++++++++++++++++
234 | miguel
23 | rene
543 | ana
23 | tlaxcala
//Richard
Comment by Richard — May 14, 2008 @ May 14, 2008 3:14 pm
Don’t think it is possible to specify an exact list like that in any of the databases I’ve worked with anyway, those being MySQL, Sybase and Oracle, do 3 selects in the order you want or 1 select and save the return values in variables and loop them in php instead.
//Richard
Comment by Richard — May 14, 2008 @ May 14, 2008 3:02 pm