SQL sorting by two columns, ignore null
mysql, sorting, sql
Solution
You can use `COALESCE` for this:
SELECT ...
ORDER BY COALESCE(`discount`, `price`), `price`, `title`
This will first order by `discount` if it's not null, and then order by `price`. For those items with the same discount, it will then order by the `price`, followed by the `title`.
Note: Depending on your desired results, you may want to remove the additional order by `price`.
Problem
I have a table with two columns like so: - price (varchar) - discount (varchar) These two hold the price and discount for all of the products in my database. When users view the products, I have multiple sorting modes available, one of which is `Sort by price: low to high`. In order to do this, I have the following code: ``` $stmt = $link->prepare("SELECT ... ORDER BY `discount` ASC, `price` ASC, `title` ASC"); ``` This works fine for all rows where `discount` is defined, but in rows where `discount` is empty, they are sorted above the others, regardless of the value of `price`. For example: ``` id|price|discount ----------------- 1|20 |10 2|25 |10 3|15 | 4|15 | ``` Will echo out in the order: ``` 3, 4, 1, 2 ``` How can I rewrite this statement to sort by `price` when `discount` has no value?