Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.