CODEWITHSUNDEEP
sqlmysqlsorting
ecstrim
ecstrim

Reputation: 263

MySql conditional order by

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

Upvotes: 26

Views: 28773

Answers (3)

Jehong Ahn
Jehong Ahn

Reputation: 2406

  1. ORDER BY CASE price WHEN 0 THEN 1 ELSE -1 END
  2. ORDER BY IF (price=0, 1, -1)
  3. ORDER BY price=0

Upvotes: 0

user272563
user272563

Reputation: 544

You can also use the following:

SELECT * 
FROM my_table 
WHERE 1  
ORDER BY price=0, price, id;

The part 'price=0' will be 1 for items with zero price, 0 for items with non-zero price. As the default sort order is ASC, non-zero items are now placed first.

The next bit of the order-by clause means that non-zero items are then sorted by price (again ascending). If any items with non-zero price have the same price, they will be further sorted by id, but we don't care about that.

The last part is only there for items where price=0. As all these items do have the same price, the effect is to sort all the zero-priced items by id.

Upvotes: 27

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196236

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

Upvotes: 51

Related Questions