Is this just a one off ORDER BY
or something that you're going to want to do often and on more values than specified here?
The order that you have given is arbitrary, therefore an identifier needs to be given to achieve what you want
SELECT
ID,
Code,
CASE Code
WHEN 'Health' THEN 1
WHEN 'Phone' THEN 2
WHEN 'Freeze' THEN 3
WHEN 'Hot' THEN 4
END As OrderBy
FROM Table
ORDER BY
OrderBy
Or
SELECT
ID,
Code
FROM Table
ORDER BY
CASE Code
WHEN 'Health' THEN 1
WHEN 'Phone' THEN 2
WHEN 'Freeze' THEN 3
WHEN 'Hot' THEN 4
END
(I'm not familiar with MySQL but the above would work in SQL Server. The syntax for MySQL won't be too different)
If you're likely to want to do this often, then create an OrderBy column on the table or create an OrderBy table with a FK link to this table and specify an OrderBy numerical field in that.