mysql query ORDER BY supplied condition

SELECT * FROM newmessage
ORDER BY id <somecondition>

here my condition is like :

(5, 3, 开发者_如何转开发2, 1, 4)

ie. i want to ORDER the result according to id like my given order above.

SELECT * FROM newmessage
    WHERE id IN (5, 3, 2, 1, 4)
    ORDER BY FIELD(id, 5, 3, 2, 1, 4)
UNION
SELECT * FROM newmessage WHERE id NOT IN (5, 3, 2, 1, 4)

You need to use a CASE statement in the ORDER BY clause. It's a bit awkward, but does the job:

SELECT * FROM newmessage m
ORDER BY CASE WHEN m.someColumn = 5 THEN 1
     WHEN m.someColumn = 3 THEN 2
     WHEN m.someColumn = 2 THEN 3
     WHEN m.someColumn = 1 THEN 4
     WHEN m.someColumn = 4 THEN 5
     ELSE m.someColumn END

EDIT : I would add, mind you, that if you have do the ability to add an 'index' or 'sort' column to the NewMessage table, then you should. Whilst my CASE hack works, it's not pretty.

One way of doing this is the fallowing:

SELECT * FROM table ORDER BY id IN(7, 13, 12) DESC;

which will sort the rows with id 7, 12 and 13 first, it will not sort in the exact order that you list in the IN() statement.

Here's how you can sort in a specific order:

SELECT * FROM table ORDER BY id = 7 DESC, id = 13 DESC, id = 12 DESC;

which will give the rows in the order that you list them. Of course, the ASC/DESC part still applies here, I just assume you need this ids first. It's a little cumbersome, but I don't think there's a more elegant way to achieve this.

It sounds like you might want to look at ORDER BY FIELD.

It allows you to specify a custom order for a given field, so you might use like this:

SELECT someField,priority FROM myTable
ORDER BY FIELD (priority,'HIGH','MEDIUM','LOW')

Which would give you priorities in that order, rather than alphabetical as would normally happen there.

So an example for the query you supplied might be:

ORDER BY FIELD(id,'5','3','2','1','4')

You can sort like this in MySQL using FIELD:

SELECT   * 
FROM     newmessage
ORDER BY FIELD(id, 5, 3, 2, 1, 4)

however:

this will probably not work as expected, when there are more rows in the table, then those specified in the ORDER clause. So you would either have to put all IDs in there or add a filter, if you want to only return these 5 rows.