メモ

ちょっと変わったソート方法をメモ。
稀に苦し紛れで使うことがあるかもしれませんが、多用はよくなさそうです。

mysql> select * from test_table;
+------+--------+------+
| id   | name   | cost |
+------+--------+------+
|    1 | apple  |  100 |
|    2 | orange |  200 |
|    3 | tomato |  120 |
|    4 | lemon  |    0 |
+------+--------+------+
4 rows in set (0.00 sec)

mysql> select * from test_table order by cost asc;
+------+--------+------+
| id   | name   | cost |
+------+--------+------+
|    4 | lemon  |    0 |
|    1 | apple  |  100 |
|    3 | tomato |  120 |
|    2 | orange |  200 |
+------+--------+------+
4 rows in set (0.00 sec)

mysql> select * from test_table order by cost = 0 asc, cost asc;
+------+--------+------+
| id   | name   | cost |
+------+--------+------+
|    1 | apple  |  100 |
|    3 | tomato |  120 |
|    2 | orange |  200 |
|    4 | lemon  |    0 |
+------+--------+------+
4 rows in set (0.00 sec)

mysql> select *, cost=0 from test_table order by cost = 0 asc, cost asc;
+------+--------+------+--------+
| id   | name   | cost | cost=0 |
+------+--------+------+--------+
|    1 | apple  |  100 |      0 |
|    3 | tomato |  120 |      0 |
|    2 | orange |  200 |      0 |
|    4 | lemon  |    0 |      1 |
+------+--------+------+--------+
4 rows in set (0.00 sec)