Help ordering records ( ORDER BY MIN() )

[Tony Dillon]

Hi Everyone,

 

I have to order a result set on the minimum value of two fields - on a
per record basis, but can't for the life of me work out how (if?) it can
be done.

 

The situation is I have a product database and products can have options
assigned. Options can have a different price from the product.  I need
to sort the results in descending order of their cost - using the
cheapest of the two price fields (either the product cost or option
cost).

 

Here's my query:

 

SELECT  base.id,  base.p_price AS main_price,  po.po_price AS opt_price

FROM products AS base LEFT JOIN productOptions AS po ON
base.id=po.po_product_id

WHERE  ( MATCH( searchTerms ) )

ORDER BY opt_price DESC, base.p_price DESC

 

And I really want to order the results by the lowest price.... 

 

I.e.

ORDER BY MIN( opt_price, base.p_price ) DESC

 

But obviously MIN works on a GROUP BY term, not on a per-record basis
which I require.

 

My goal is to list products in descending order of cheapest cost.

 

Hope you can help,

Many thanks in advance,

Tony