SELECT t1.id,t1.groupid,t1.slug,t1.customgift, t1.VAT ,t1.sale,t1.nameen,t1.code,t1.brand,min(t1.price) as 'min',max(t1.price) as 'max',(SELECT t5.nameen FROM productgroups as t5 WHERE t1.groupid=t5.id) as 'gname', (SELECT img FROM brands as t3 WHERE t3.id=t1.brand LIMIT 1) as 'bran', (SELECT slug FROM productgroups as t5 WHERE t5.id=t1.groupid LIMIT 1) as 'groupslug', (SELECT enddate FROM sale as t4 WHERE t4.code=t1.code ORDER BY id DESC LIMIT 1) as 'enddate', (SELECT startdate FROM sale as t4 WHERE t4.code=t1.code ORDER BY id DESC LIMIT 1) as 'startdate', (SELECT AVG(star) FROM reviews as t2 WHERE t1.id=t2.pid LIMIT 1) as 'star', (SELECT salepercent FROM sale as t10 WHERE t10.code=t1.code ORDER BY id DESC LIMIT 1) as 'slperc', (SELECT maxval FROM couplimit AS t11 WHERE t11.pid=t1.id LIMIT 1) as 'maxvall', (SELECT cartsaletype FROM pcategories as t9 WHERE t9.id=t1.category LIMIT 1) as 'cartsaletype' FROM products as t1 WHERE t1.id>0 AND t1.active=1 AND t1.instock>0 AND t1.brand<>1061 AND t1.price>='35' AND t1.price<='550' AND FIND_IN_SET(category,'34,94,95,96,98,99,102,127,200') AND !(t1.brand=23 AND t1.category='147') AND !(t1.brand=32 AND t1.category='141') AND t1.brand<>29 GROUP BY t1.groupid ORDER BY t1.id DESC LIMIT 20 OFFSET 3140