I'm having a problem with an order by in a select statement. The whole statement is:
SELECT listing_details.*,
listings.listing_date as list_date,
listings.exp_date as expr_date,
listings.list_price,
listings.red_price1,
listings.red_price2,
listings.red_price3,
listings.red_price4,
pending.id as pendid,
pending.closed as close_date,
pending.withdrawn
FROM listing_details
LEFT JOIN listings on listing_details.id=listings.id
LEFT JOIN pending on pending.id=listing_details.id
WHERE listing_details.status='Listing'
ORDER BY LENGTH(listings.list_price) DESC,
listings.list_price DESC,
listings.red_price1 DESC
I'm trying to sort out a way so that it is actually ordered by the price reductions (if they exist- red_price1 - 4) instead of the list_price field. I have other code that determines which to display in a printf and have no problem displaying the latest reduction it's just that it isn't being ordered properly. The actual order should be if there is a red_price4 it should be ordered by that, then 3 2 1 if none of those then it should be ordered by list_price. Ideas?
Thanks in advance!
