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
add comment

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.