Sort MySQL query by one match, then usually

Question

I am new to MySQL queries, but I have one difficult thing that I cannot figure out how to do this. Is there a way to tell the system "something that meets the X-condition, first of all, if this is normal." For example, in a table postswith fields, (post_id, author_id, timestamp)I know how to do this ...

mysql_query("SELECT * FROM posts ORDER BY timestamp");

But is there a way for certain user messages (say, author_id = 5) to appear at the top of the results, and then sort by timestamp after that? To be clear: I do NOT want to sort by author_id, I just want to highlight messages where author_idthere are 5, and then sort by timestampfor everything else.

Is there any way to do this? I tried,

mysql_query("SELECT * FROM posts ORDER BY author_id = 5, timestamp");

... but it does not work.

Answer 1

Try the following:

SELECT * FROM posts ORDER BY FIELD(author_id, '5') DESC, timestamp

Answer 2

ORDER BY CASE WHEN author_id=5 THEN 0 ELSE 1 END , timestamp

This works for most RDBMSs, for using MySQL specfic use @notulysses answers.

Answer 3

I think the Reza request would look something like this:

SELECT  * FROM posts WHERE author_id = 5
UNION ALL
(SELECT * FROM posts WHERE author_id <> 5 ORDER BY timestamp)

This will put author_id5 results at the top, the rest - by timestamp.