SELECT DISTINCT *, `pd`.`name`, `p`.`image`, (SELECT (CASE WHEN `pd2`.`type` = 'P' THEN (`p`.`price` - (`p`.`price` * (`pd2`.`price` / 100))) WHEN `pd2`.`type` = 'S' THEN (`p`.`price` - `pd2`.`price`) ELSE `pd2`.`price` END) FROM `oc_product_discount` `pd2` WHERE `pd2`.`product_id` = `p`.`product_id` AND `pd2`.`customer_group_id` = '1' AND `pd2`.`quantity` = '1' AND `pd2`.`special` = '0' AND ((`pd2`.`date_start` = '0001-01-01' OR `pd2`.`date_start` < NOW()) AND (`pd2`.`date_end` = '0001-01-01' OR `pd2`.`date_end` > NOW())) ORDER BY `pd2`.`priority` ASC, `pd2`.`price` ASC LIMIT 1) AS `discount`, (SELECT (CASE WHEN `ps`.`type` = 'P' THEN (`p`.`price` - (`p`.`price` * (`ps`.`price` / 100))) WHEN `ps`.`type` = 'S' THEN (`p`.`price` - `ps`.`price`) ELSE `ps`.`price` END) FROM `oc_product_discount` `ps` WHERE `ps`.`product_id` = `p`.`product_id` AND `ps`.`customer_group_id` = '1' AND `ps`.`quantity` = '1' AND `ps`.`special` = '1' AND ((`ps`.`date_start` = '0001-01-01' OR `ps`.`date_start` < NOW()) AND (`ps`.`date_end` = '0001-01-01' OR `ps`.`date_end` > NOW())) ORDER BY `ps`.`priority` ASC, `ps`.`price` ASC LIMIT 1) AS `special`, (SELECT `pr`.`points` FROM `oc_product_reward` `pr` WHERE `pr`.`product_id` = `p`.`product_id` AND `pr`.`customer_group_id` = '1') AS `reward`, (SELECT COUNT(*) FROM `oc_review` `r` WHERE `r`.`product_id` = `p`.`product_id` AND `r`.`status` = '1' GROUP BY `r`.`product_id`) AS `reviews` FROM `oc_category_to_store` `c2s` LEFT JOIN `oc_category_path` `cp` ON (`cp`.`category_id` = `c2s`.`category_id` AND `c2s`.`store_id` = '0') LEFT JOIN `oc_product_to_category` `p2c` ON (`p2c`.`category_id` = `cp`.`category_id`) LEFT JOIN `oc_product_to_store` `p2s` ON (`p2s`.`product_id` = `p2c`.`product_id` AND `p2s`.`store_id` = '0') LEFT JOIN `oc_product` p ON (p2c.product_id = p.product_id) LEFT JOIN `oc_product_description` `pd` ON (`p`.`product_id` = `pd`.`product_id`) WHERE `pd`.`language_id` = '2' AND `cp`.`path_id` = '68' GROUP BY `p`.`product_id` ORDER BY p.sort_order ASC, LCASE(`pd`.`name`) ASC LIMIT 0,1000

SELECT DISTINCT *, `pd`.`name`, `p`.`image`, (SELECT (CASE WHEN `pd2`.`type` = 'P' THEN (`p`.`price` - (`p`.`price` * (`pd2`.`price` / 100))) WHEN `pd2`.`type` = 'S' THEN (`p`.`price` - `pd2`.`price`) ELSE `pd2`.`price` END) FROM `oc_product_discount` `pd2` WHERE `pd2`.`product_id` = `p`.`product_id` AND `pd2`.`customer_group_id` = '1' AND `pd2`.`quantity` = '1' AND `pd2`.`special` = '0' AND ((`pd2`.`date_start` = '0001-01-01' OR `pd2`.`date_start` < NOW()) AND (`pd2`.`date_end` = '0001-01-01' OR `pd2`.`date_end` > NOW())) ORDER BY `pd2`.`priority` ASC, `pd2`.`price` ASC LIMIT 1) AS `discount`, (SELECT (CASE WHEN `ps`.`type` = 'P' THEN (`p`.`price` - (`p`.`price` * (`ps`.`price` / 100))) WHEN `ps`.`type` = 'S' THEN (`p`.`price` - `ps`.`price`) ELSE `ps`.`price` END) FROM `oc_product_discount` `ps` WHERE `ps`.`product_id` = `p`.`product_id` AND `ps`.`customer_group_id` = '1' AND `ps`.`quantity` = '1' AND `ps`.`special` = '1' AND ((`ps`.`date_start` = '0001-01-01' OR `ps`.`date_start` < NOW()) AND (`ps`.`date_end` = '0001-01-01' OR `ps`.`date_end` > NOW())) ORDER BY `ps`.`priority` ASC, `ps`.`price` ASC LIMIT 1) AS `special`, (SELECT `pr`.`points` FROM `oc_product_reward` `pr` WHERE `pr`.`product_id` = `p`.`product_id` AND `pr`.`customer_group_id` = '1') AS `reward`, (SELECT COUNT(*) FROM `oc_review` `r` WHERE `r`.`product_id` = `p`.`product_id` AND `r`.`status` = '1' GROUP BY `r`.`product_id`) AS `reviews` FROM `oc_category_to_store` `c2s` LEFT JOIN `oc_category_path` `cp` ON (`cp`.`category_id` = `c2s`.`category_id` AND `c2s`.`store_id` = '0') LEFT JOIN `oc_product_to_category` `p2c` ON (`p2c`.`category_id` = `cp`.`category_id`) LEFT JOIN `oc_product_to_store` `p2s` ON (`p2s`.`product_id` = `p2c`.`product_id` AND `p2s`.`store_id` = '0') LEFT JOIN `oc_product` p ON (p2c.product_id = p.product_id) LEFT JOIN `oc_product_description` `pd` ON (`p`.`product_id` = `pd`.`product_id`) WHERE `pd`.`language_id` = '2' AND `cp`.`path_id` = '68' GROUP BY `p`.`product_id` ORDER BY p.date_added DESC, LCASE(`pd`.`name`) DESC LIMIT 0,12