MySQL - Merging results of two rows into one and sorting

[pblancher]

I have a product list that has fields for two prices ("sale_price" and "retail_price"). When I run a query to sort listings by price:

SELECT * from product_list WHERE sku='39393939393' ORDER BY sale_price ASC, retail_price ASC;

I get a sorted list but first it sorts the "sale_price" items, then "retail_price". What I want it to do is to sort all prices wither it's sale or not. This way if one match record has the regular price 24.99 but two sale items are 22.99 and 26.99, it will show like:

22.99 < Sale
24.99 < Regular
26.99 < Sale

Any suggestions on how to do that?

[NogDog]

You could use a union:

(SELECT product_id, description, 'Reg.' AS type, reg_price AS price 
   FROM products)
UNION
(SELECT product_id, description, 'Sale' AS type, sale_price AS price 
   FROM products)
ORDER BY price ASC