MySql conditional order by

mysql, sorting, sql

Solution

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

Problem

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: - items with `price > 0.00` first, ordered by `price ASC` - 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

Original source