SELECT p.id_product, pl.product, pl.introduction, pic.picture, p.featured, p.new, IF(fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1, 1, 0) AS is_offer, fr.price AS price, MAX(fr.offer_price) AS offer_price, IF (fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1, fr.offer_price, fr.price) AS act_price, IF (p.featured_start <= NOW() AND p.featured_end > NOW() AND p.featured=1, 1, 0) AS is_featured, IF (p.new_start <= NOW() AND p.new_end > NOW() AND p.new=1, 1, 0) AS is_new FROM ct_products p LEFT JOIN ct_product_languages pl ON pl.id_product = p.id_product AND pl.id_language = 'es' LEFT JOIN ct_pictures pic ON pic.id_product = p.id_product AND pic.`order`=1 LEFT JOIN ct_product_categories pc ON pc.id_product=p.id_product LEFT JOIN ct_formats f ON f.id_product=p.id_product LEFT JOIN ct_format_rates fr ON fr.id_format=f.id_format AND fr.id_rate=1 WHERE fr.id_format IS NOT NULL AND fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1 AND product <> '' GROUP BY f.id_format ORDER BY pl.product ASC, is_offer DESC, offer_price ASC, price ASC

SELECT p.id_product, pl.product, pl.introduction, pic.picture, p.featured, p.new, IF(fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1, 1, 0) AS is_offer, fr.price AS price, MAX(fr.offer_price) AS offer_price, IF (fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1, fr.offer_price, fr.price) AS act_price, IF (p.featured_start <= NOW() AND p.featured_end > NOW() AND p.featured=1, 1, 0) AS is_featured, IF (p.new_start <= NOW() AND p.new_end > NOW() AND p.new=1, 1, 0) AS is_new FROM ct_products p LEFT JOIN ct_product_languages pl ON pl.id_product = p.id_product AND pl.id_language = 'es' LEFT JOIN ct_pictures pic ON pic.id_product = p.id_product AND pic.`order`=1 LEFT JOIN ct_product_categories pc ON pc.id_product=p.id_product LEFT JOIN ct_formats f ON f.id_product=p.id_product LEFT JOIN ct_format_rates fr ON fr.id_format=f.id_format AND fr.id_rate=1 WHERE fr.id_format IS NOT NULL AND p.new_start <= NOW() AND p.new_end > NOW() AND p.new=1 AND product <> '' GROUP BY f.id_format ORDER BY pl.product ASC, is_offer DESC, offer_price ASC, price ASC

SELECT p.id_product, pl.product, pl.introduction, pic.picture, p.featured, p.new, IF(fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1, 1, 0) AS is_offer, fr.price AS price, MAX(fr.offer_price) AS offer_price, IF (fr.offer_start <= NOW() AND fr.offer_end > NOW() AND fr.offer=1, fr.offer_price, fr.price) AS act_price, IF (p.featured_start <= NOW() AND p.featured_end > NOW() AND p.featured=1, 1, 0) AS is_featured, IF (p.new_start <= NOW() AND p.new_end > NOW() AND p.new=1, 1, 0) AS is_new FROM ct_products p LEFT JOIN ct_product_languages pl ON pl.id_product = p.id_product AND pl.id_language = 'es' LEFT JOIN ct_pictures pic ON pic.id_product = p.id_product AND pic.`order`=1 LEFT JOIN ct_product_categories pc ON pc.id_product=p.id_product LEFT JOIN ct_formats f ON f.id_product=p.id_product LEFT JOIN ct_format_rates fr ON fr.id_format=f.id_format AND fr.id_rate=1 WHERE fr.id_format IS NOT NULL AND p.featured_start <= NOW() AND p.featured_end > NOW() AND p.featured=1 AND product <> '' GROUP BY f.id_format ORDER BY pl.product ASC, is_offer DESC, offer_price ASC, price ASC