Alex Rivera | Logout

MySql conditional order by

Asked 2010-02-13T17:15:09.823
26

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

Edit
Report

1 Answer

51

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
answered 2010-02-13T17:28:22.527

Your Answer