MySQL : Conditional ORDER BY to only one column Hello and thanks for taking time to read this question.

I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific criteria to this column. For example, to the following table, I want to ORDER BY 'group', showing first the 9,7,6 'group' items and, in the end 10,8,5 'group' items:

names     group
--------- ------
susanita  10
miguelito 5
mafalda   7
manolito  8
libertad  6
felipe    9
guille    8


thanks in advance.
SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');


I took your sample data, loaded it into a table called mytable and ran it.

Here are the results:

mysql&gt; use test
Database changed
mysql&gt; drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)

mysql&gt; create table mytable
    -&gt; (
    -&gt;    names varchar(10),
    -&gt;    `group` int
    -&gt; );
Query OK, 0 rows affected (0.08 sec)

mysql&gt; insert into mytable values
    -&gt; ('susanita',  10),
    -&gt; ('miguelito', 5),
    -&gt; ('mafalda',   7),
    -&gt; ('manolito',  8),
    -&gt; ('libertad',  6),
    -&gt; ('felipe',    9),
    -&gt; ('guille',    8);
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM mytable;
+-----------+-------+
| names     | group |
+-----------+-------+
| susanita  |    10 |
| miguelito |     5 |
| mafalda   |     7 |
| manolito  |     8 |
| libertad  |     6 |
| felipe    |     9 |
| guille    |     8 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql&gt; SELECT * FROM mytable ORDER BY
    -&gt; LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.01 sec)

mysql&gt;


Give it a Try !!!

UPDATE 2011-09-06 12:33 EDT

Here is another approach:

SELECT* FROM mytable ORDER BY
IF(FIELD(`group`,9,7,6,10,8,5)=0,99999,FIELD(`group`,9,7,6,10,8,5));


This will force any groups other than 9,7,6,10,8,5 to appear at the very bottom of the query.

UPDATE 2011-09-06 14:39 EDT

mysql&gt; SELECT names, `group`
    -&gt; FROM mytable
    -&gt; WHERE `group` IN (9,7,6,10,8,5)
    -&gt; ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.00 sec)


Hey @Nick, yours works as well against my sample data !!!