SET @cid = 0;
SET @a = 1;
SELECT *,
		IF (@cid = category_id, @a:=@a+1, @a:=1) AS RowNum,
		@cid:=category_id
FROM ( SELECT 
			p.product_id, pd.name Name, p.date_added, p2c.category_id
		FROM oc_product p
			LEFT JOIN oc_product_description pd ON p.product_id = pd.product_id
			LEFT JOIN oc_product_to_category p2c ON p2c.product_id =  p.product_id
			WHERE pd.language_id = 1
			AND p.status = 1
		ORDER BY p2c.category_id, p.date_added, p.product_id
) B
Having RowNum < 10
ORDER BY category_id, RowNum

SELECT 
	SUBSTRING_INDEX(GROUP_CONCAT(CONCAT(p.product_id,':',pd.name) ORDER BY p.product_id DESC SEPARATOR ','), ',', 15) AS products, 
	p2c.category_id
FROM oc_product p
	LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id)
	LEFT JOIN oc_product_description pd ON p.product_id = pd.product_id
WHERE pd.language_id = 1
	AND p.status = 1

GROUP BY p2c.category_id
ORDER BY p2c.category_id,p.date_added DESC