How do I order a list in MySQL so that a specific id always shows up first?

MySQL

+----+
| id |
+----+
| 1  |
+----+
| 2  |
+----+
| 3  |
+----+
| 4  |
+----+

How do I order this list, so that 2 always shows up first? It should output 2, 1, 3, 4, and not 1, 2, 3, 4. Or I could also pick 3 to show up first, so it would outp开发者_开发知识库ut 3, 1, 2, 4.

PHP

$a = mysql_query("SELECT * FROM table ORDER BY ???");

---

SELECT * FROM table 
ORDER BY CASE WHEN ID = 2 THEN 0 ELSE ID END

---

I use this trick:

SELECT
   IF (`id` = 2, -1, `id`) AS `weight`
FROM `table` ORDER BY `weight` ASC;

I like Chris' answer, It's the best method for sure! (You learn something every day)

---

Another trick is to use field() function

select * from table order by field(2,id) desc,id