Menu
I am trying to build a MySQL query that has an order by statement. This is what I am trying to do:
SELECT *
FROM tbl_product
ORDER BY retail_price ONLY IF wholesale_price IS NULL OTHERWISE ORDER BY wholesale_price.
I have no idea where to start. I found an article that uses ORDER BY COALESCE but I also found that this could have performance issues.
Any advice is appreciated.
934
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
using ORDER BY NULL is a workaround that satifies the syntax requirement but does not actually change the order of the data. In effect it is an instruction to not order at all. N.B.: some (myself included) prefer to use SELECT 1 instead of SELECT NULL but there is no difference in effect.
SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
Ordering. When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. So ordering in DESC order will see the NULLs appearing last. To force NULLs to be regarded as highest values, one can add another column which has a higher value when the main field is NULL.
SELECT *
FROM tbl_product
ORDER BY ifnull(wholesale_price, retail_price);
Note that you don't need to select the value you're ordering by - it can be any expression
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
