sorting on 2 columns together

[Jeremy21b]

Say I have a database with 2 price columns, price & sale price. Is there any way I could sort by both together? Sale price would be null if the product wasn't on special. I want to sort the query by price, but I don't know how to make it take the special price into account. I might be stuck outputting to an array and then looping through it.

 

[DreXor]

select price, saleprice from products order by price, saleprice

first sorts by price, where the prices are the same then it'll sort the sale prices accordingly

select price, saleprice from products order by saleprice, price

will sort saleprice first, and where sale prices are the same (example null saleprices) then price will be sorted accordingly.


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Jeremy21b]

Unfortunately that won't accomplish what I want. Say I have products priced $1, $2, $3, $4, but the product for $3 is on sale for $2. That product would show up in the wrong order. I think a UNION might be my solution.

It actually gets more complicated though. There is also a 3rd price column for a different currency. As it is in a different currency I would really need to convert it before sorting it.