SELECT t1.id,t1.groupid,t1.slug,t1.customgift, t1.VAT ,t1.sale,t1.namege,t1.code,t1.brand,min(t1.price) as 'min',max(t1.price) as 'max',(SELECT t5.namege 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.brand='98') AND !(t1.brand=23 AND t1.category='147') AND !(t1.brand=32 AND t1.category='141') AND t1.brand<>29 AND ( FIND_IN_SET('MIW-FILTERS',t1.specs)) GROUP BY t1.groupid ORDER BY t1.id DESC LIMIT 20 OFFSET 0