MySQL question ORDER BY `user_id` IN (1,2,3) AND `name`

I have been searching for an existing question for this one, but i am not sure what to search for. I am looking for a way to order my results based on a list of id's and a second variable.

Imagine the following record set

user_id  user_name
------------------
   1       Peter
   2       John
   3       Luke
   4       Tim
   5       George
   6       Michael
   ~       etcetera

Now, I have a list of user ids (lets say 1,4,5) that I want the result of my query to start with.

So i thought:

 // Edit: replaced 'AND' with ','
 SELECT * FROM `user` ORDER BY `user_id` IN (1,4,5), `name`

Would return something like:

user_id  user_name
------------------
   1       Peter   <-- Notice the 1,4,5 at the top
   4       Tim
   5       George
   2       John    <-- After that its just alphabetical
   3       Luke
   6       Michael
   ~       etcetera

But it doesn't.. It looks like it just ignores the sorting 开发者_JS百科directives. Does anyone know what I am doing wrong? Is it even possible to do something like this?

Try this:

 SELECT * 
        FROM    `user` 
    ORDER BY 
        CASE 
            WHEN `user_id` IN (1,4,5) THEN 1
            ELSE `user_id`
        END,
        `name` 

SELECT *
FROM user 
ORDER BY
   CASE WHEN user_id IN (1,4,5) THEN 0 ELSE 1 END,
   CASE WHEN user_id IN (1,4,5) THEN user_id ELSE 1 END,
   name

Edit: removed WHERE

You could do the two parts separately and stick them together with a UNION...

SELECT * FROM user 
WHERE user_id IN (1,4,5)
ORDER BY user_id

UNION

SELECT * FROM user 
WHERE user_id NOT IN (1,4,5)
ORDER BY user_name

Try changing the AND to a ,

SELECT * FROM `user` ORDER BY `user_id` IN (1,4,5) desc, `name`

The query you want is this one

SELECT * FROM user
ORDER BY
IF(FIELD(user_id,1,4,5)=0,99999,FIELD(user_id,1,4,5)),user_name;

I loaded you sample data and ran the query in MySQL 5.5.12 in Windows

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: USE example
Database changed
MySQL (Current example) :: DROP TABLE IF EXISTS user;
  user_name VARCHAR(20)
);
INSERT INTO user VALUES
(1,'Peter'),(2,'John'),
(3,'Luke'),(4,'Tim'),
(5,'George'),(6,'Michael');
SELECT * FROM user;
SELECT * FROM user
ORDER BY
IF(FIELD(user_id,1,4,5)=0,99999,FIELD(user_id,1,4,5)),user_name;
Query OK, 0 rows affected (0.02 sec)

MySQL (Current example) :: CREATE TABLE user
    -> (
    ->   user_id INT,
    ->   user_name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.08 sec)

MySQL (Current example) :: INSERT INTO user VALUES
    -> (1,'Peter'),(2,'John'),
    -> (3,'Luke'),(4,'Tim'),
    -> (5,'George'),(6,'Michael');
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current example) :: SELECT * FROM user;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | Peter     |
|       2 | John      |
|       3 | Luke      |
|       4 | Tim       |
|       5 | George    |
|       6 | Michael   |
+---------+-----------+
6 rows in set (0.00 sec)

MySQL (Current example) :: SELECT * FROM user
    -> ORDER BY
    -> IF(FIELD(user_id,1,4,5)=0,99999,FIELD(user_id,1,4,5)),user_name;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | Peter     |
|       4 | Tim       |
|       5 | George    |
|       2 | John      |
|       3 | Luke      |
|       6 | Michael   |
+---------+-----------+
6 rows in set (0.00 sec)

MySQL (Current example) ::

Give it a Try !!!

UPDATE 2011-07-26 13:37 EDT

If the order of the list matters, simply write the list as needed.

EXAMPLE : if you need the list ordered 4,1,5 then the query should look like this:

SELECT * FROM user
ORDER BY
IF(FIELD(user_id,4,1,5)=0,99999,FIELD(user_id,4,1,5)),user_name;

Here is the execution with the same sample data:

MySQL (Current example) :: SELECT * FROM user
    ->     ORDER BY
    ->     IF(FIELD(user_id,4,1,5)=0,99999,FIELD(user_id,4,1,5)),user_name;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       4 | Tim       |
|       1 | Peter     |
|       5 | George    |
|       2 | John      |
|       3 | Luke      |
|       6 | Michael   |
+---------+-----------+
6 rows in set (0.00 sec)

MySQL (Current example) ::