SQL sorting by two columns, ignore null

Question

I have a table with two columns like so:

1. price (varchar)
2. 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?

Answer 1

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.