mySQL语句ORDER BY多列

Question

If I have two columns, prices (full_price, sales_price), and these columns has numbers in them. I know that sql statements can do multiple order by's but how does it work when these columns have values in them?

("SELECT * FROM table ORDER BY full_price,sales_price DESC")

How do I accomplish so it will pick the smallest value of the two columns? and it will place the data in order based on the chosen column between full_price and sales_price?

Note: sometimes sales_price will have no value.

Thanks

Edit:

Example

id    full_price        sales_price
1      23                 42           
2      342                200
3      1
4      10                 8

What I'm trying to do is with these numbers, I can output data associated with the lowest price.

The order should be:

3,4,1,2

Reason:

3: 1
4: 8
1: 23
2: 200

Answer 1

Assuming that your blank sales_price is a NULL and that full_price cannot be NULL:

select ...
from ...
where ...
order by case
    when sales_price is null then full_price
    else least(full_price, sales_price)
end

You probably want to add a secondary sort key to get consistent and sensible results from ties.