mysql query ORDER BY supplied condition

Question

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.

And what is your question about it? I guess something along the line of "Why doesn't it work", right? You might want to add that. :) The problem is, that SQL doesn't work this way. ORDER BY just tells the database the column to sort by...not the method of sorting. — Till Helge, Oct 13 '11 at 11:29
you are so right - SQL i s divided into five parts: columns you want the way they join grouping filtering (where) and finally in which order: ascending or descending i think there is lack of knowledge here ... — MikeyKennethR, Oct 13 '11 at 11:38
There are ways to define your own method of sorting. Not exactly pretty, but it can be done :) — Nikoloff, Oct 13 '11 at 11:39
Duplicate of [MySQL ORDER BY IN()](http://stackoverflow.com/questions/1332434/mysql-order-by-in). — hakre, Oct 13 '11 at 11:55

score 4 · Accepted Answer · answered Oct 13 '11 at 11:36

4

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)

answered Oct 13 '11 at 11:36

Marco

56,740
14
129
152

Widor · Answer 2 · 2011-10-13T11:41:49.353

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.

score 1 · Answer 3 · answered Oct 13 '11 at 11:34

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.

score 1 · Answer 4 · answered Oct 13 '11 at 11:35

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

score 1 · Answer 5 · answered Oct 13 '11 at 11:35

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.

mysql query ORDER BY supplied condition

5 Answers5