this isn't the usual Select top N results from each category question - there's a little bit more to it.
I have the Select top N query as follows:
SELECT tmp.id, tmp.name, tmp.category_id, tmp.merchant_id, rating, rank FROM ( SELECT id, name, category_id, merchant_id, rating, IF( @prev <> p.category_id, @rownum := 1, @rownum := @rownum+1 ) AS rank, @prev := p.category_id FROM products p JOIN (SELECT @rownum := NULL, @prev := 0) AS r ORDER BY p.category_id, rating DESC ) AS tmp WHERE tmp.rank <= 2 ORDER BY category_id ;
Which gives the top 2 results of each category as expected.
But, what I want is the top 2 products of each category EXCLUDING any where the merchant has already been picked out as the top N of a another category.
I want to try an avoid creating another table at this stage, but if that's the only way to go then so be it.
Thanks in advance