Q:

MySQL order by using some attribute

I got a table category as follow
category_id
1
2
3
4
5
6
7
8
I ran SELECT category_id from category order by category_id > 3 DESC, category_id;
the result is
category_id
4
5
6
7
8
1
2
3
 can anyone explain
1) why this result?
2) what does "order by category_id > 3" ?
3) what does the second expression "category_id" do?

A:

Your current ORDER BY clause has two sorting levels:
ORDER BY
    category_id > 3 DESC,
    category_id;

The first level category_id > 3 is a boolean expression, and will be 0 (false) or 1 (true).  But because you order this level descending, the true case will be first.  This means that all category_id values greater than 3 will appear before all category_id values less than 3.
Then, the second sort order is category_id.  This means that within each of the two above mentioned groups, the values will be ordered ascending by the category_id.

