MySql conditional order by

Question

I have this table (simplified):

CREATE TABLE `my_table` (   `id` INT NOT NULL AUTO_INCREMENT ,   `item_name` VARCHAR(45) NULL ,   `price` DECIMAL(10,0) NULL ,   PRIMARY KEY (`id`)  ) 

I need to select all items from the table, ordered this way:

1. items with price > 0.00 first, ordered by price ASC
2. items with price = 0.00 last, ordered by id

I tried this:

SELECT *  FROM my_table  WHERE 1   ORDER BY    CASE price WHEN !0.00 THEN price     ELSE id   END  ASC 

And I get results like

item_name	price
foo	150,00
bar	0,00
baz	500,00
hum	0,00

How do I build the query to have

item_name	price
foo	150,00
baz	500,00
bar	0,00
hum	0,00

Thank you for your time

Answer 1

This will do the trick..

 SELECT *      FROM my_table      WHERE 1       ORDER BY        CASE price WHEN 0 THEN 1        ELSE -1       END ASC, price asc, id asc