Updated 16/2/13

The following will list all active products with their most recent fixed and unit prices.

This has the limitation that if you don't set a start_time for a product price (i.e the start_time is null), then incorrect results will be returned.

 select e.name as product, e.arch_short_name as archetype, if(p1.price,p1.price,0.00) as fixedPrice, if(p2.price,p2.price,0.00) as unitPrice,
    d1.value as uom, p1.product_price_id as fixedPriceId, p2.product_price_id as unitPriceId
from products p
join entities e on p.product_id = e.entity_id
left join
    (select product_id, max(start_time) as start_time
     from product_prices
     where arch_short_name = "productPrice.fixedPrice"
     group by product_id) as p1max on p1max.product_id = p.product_id
left join product_prices p1 on p1.product_id = p.product_id and p1.start_time = p1max.start_time and p1.arch_short_name = "productPrice.fixedPrice"    
left join
    (select product_id, max(start_time) as start_time
     from product_prices
     where arch_short_name = "productPrice.unitPrice"
     group by product_id) as p2max on p2max.product_id = p.product_id
left join product_prices p2 on p2.product_id = p.product_id and p2.start_time = p2max.start_time and p2.arch_short_name = "productPrice.unitPrice"    
left join entity_details d1 on d1.entity_id = e.entity_id and d1.name = "sellingUnits"
left join entity_classifications productgroup on productgroup.entity_id = e.entity_id
left join lookups grouplookup on grouplookup.lookup_id = productgroup.lookup_id
left outer join entity_relationships rpt on rpt.target_id = e.entity_id and rpt.arch_short_name = "entityRelationship.productTypeProduct"
left outer join entities producttype on producttype.entity_id = rpt.source_id
where e.active = 1
order by product

-Tim