Q: mysql how to order by user defined order/sort by number of items on a field mysql how to order by user defined order/sort 
say for a table 
---------+----------
name     | category
---------+----------
apple    | 0
orange   | 0
book     | 1
notebook | 1
textboo  | 1
phone    | 2

How to order it in the following order of category ie category=1, category=0, category=2
to get the view as
---------+----------
name     | category
---------+----------
book     | 1
notebook | 1
textbook | 1
apple    | 0
orange   | 0
phone    | 2

How do we write an sql for this?
Also better, if the statement can identify and sort desc based on the number of items on each category.

A: You want to do this:
SELECT Name, Category
FROM MyTable
ORDER BY 
    Case category
        when 1 then 1
        when 0 then 2
        else 3
    end,
    Name

Update
In the first answer, the order is fixed by category. When ordering by the number of items in category, you want to do this:
select name, Category, 
       (select count(*) from MyTable mt2 where mt2.Category = mt1.category) CatCount
from MyTable mt1
order by 3 DESC, name


A: If you want to sort by the number of entries in the category, you can do:
SELECT my_table.name, my_table.category, cats.total FROM
    (SELECT category, COUNT(*) AS total FROM my_table GROUP BY category) cats
    INNER JOIN my_table ON my_table.category = cats.category
    ORDER BY cats.total DESC, my_table.name ASC


A: If you know the order while writing the query you can use UNION ALL:
SELECT name, category
FROM table
WHERE category = 1

UNION ALL

SELECT name, category
FROM table
WHERE category = 0

UNION ALL

SELECT name, category
FROM table
WHERE category = 2

