Mysql selecting top 10 of each category analytic function

Question

I have a table which has the following two columns including others: rating, price and code. Ratings is similar to a category. I wish to generate the top 10 for each rating order by price ascending where code = 'ABC'. I'm at a loss to figure out where to put the last two conditions in the following mysql statement. Please can someone advise. Many thanks

SELECT x.*
  FROM (SELECT t.*,
               CASE 
                 WHEN @rating != t.rating THEN @rownum := 1
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @rating := t.rating AS var_rating
          FROM offers t
          JOIN (SELECT @rownum := NULL, @rating := '') r
      ORDER BY t.rating) x
 WHERE x.rank <= 10

ALso, what if the rating column had entries like 1, 1, 1k, 1*, 1+, 2, 2, 2+, 3,3,3* etc, how would I be able to consider all of these entries as '1', 2 and 3 respectively in the same sql statement?

Answer 1

Try this:

SELECT x.*
  FROM (SELECT t.*,
               CASE 
                 WHEN @rating != t.rating THEN @rownum := 1
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @rating := t.rating AS var_rating
          FROM offers t
          JOIN (SELECT @rownum := NULL, @rating := '') r
         WHERE code = 'ABC'
      ORDER BY t.rating, price) x
 WHERE x.rank <= 10

The changes are:

• Added a WHERE clause after the JOIN clause
• Added price to the ORDER BY clause.