MySql conditional order by
Solution 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
Solution 2
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.
Related videos on Youtube
ecstrim
Updated on July 09, 2022Comments
-
ecstrim almost 2 years
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 byprice ASC
- items with
price = 0.00
last, ordered byid
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
- items with
-
ecstrim about 14 yearsWonderfull, thank you very much! One question, the 1 and -1 from THEN and ELSE what do they mean?
-
Matthew Flaschen about 14 yearsIt means when price is 0, assign 1 for the sort, otherwise assign -1. Any two values would work, as long as the THEN value is greater than the ELSE value.
-
Gabriele Petrioli about 14 yearsWhat @Matthew mentioned. We give a value to each row (of 1 or -1) according to its price (0 or greater) and then sort by that value. So the rows with -1 will be first (those with a price >0) and those with 1 will be last (those with price = 0).