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

Question

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 directives. Does anyone know what I am doing wrong? Is it even possible to do something like this?

Maybe adding another column for sorting and give them numbers and sort it by this new column. — Warface, Jul 27 '11 at 16:46
@Warface: I'm afraid the user_id list is dynamic. It is a different list for every user. — Johan, Jul 27 '11 at 16:47
@stereofrog Thanks, that looks like what I was looking for! I will check them out. — Johan, Jul 27 '11 at 16:53

Richard Inglis · Answer 1 · 2011-07-27T17:02:24.510

3

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

edited Jul 27 '11 at 17:02

answered Jul 27 '11 at 16:47

Richard Inglis

5,888
2
33
37

1

Dang, beat me to the punch. :) – Paul Sonier Jul 27 '11 at 16:47

score 3 · Accepted Answer · answered Jul 27 '11 at 16:47

3

Try this:

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

answered Jul 27 '11 at 16:47

Chandu

81,493
19
133
134

2

+1 I would set the result of the `ELSE` statement to 2 instead of `user_id`, though – soulmerge Jul 27 '11 at 16:49
I went for this solution, replacing the "ELSE user_id" with "ELSE 2". Thanks guys! You made my day :) – Johan Jul 27 '11 at 17:05

gbn · Answer 3 · 2011-07-27T16:56:35.703

3

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

edited Jul 27 '11 at 16:56

answered Jul 27 '11 at 16:49

gbn

422,506
82
585
676

Jacob Eggers · Answer 4 · 2011-07-27T17:10:06.740

2

Try changing the AND to a ,

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

edited Jul 27 '11 at 17:10

answered Jul 27 '11 at 16:48

Jacob Eggers

9,062
2
25
43

Hah, indeed, that explains why it was ignoring my sort directives :P Thanks – Johan Jul 27 '11 at 16:51

RolandoMySQLDBA · Answer 5 · 2011-07-27T17:36:50.720

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) ::

Wow, thanks! +1 for your trouble :) I already have a working query thanks to Cybernate and gbn but I will have a look at performance differences. — Johan, Jul 27 '11 at 17:17

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

5 Answers5