title: How to get top 10 items based on category in joined table
body: <p>I can get the top 1000 selling items from a sales table in a day (and display item info from another table) with the following query:</p>
  <pre><code> select
      cs.item_no, sum(quantity) as quantity, i.item_type, i.item_name, i.group_name
      FROM `sales` cs
      left join 
      (
      SELECT 
                  item_no,
                  MIN(item_name) as item_name,
                  MIN(item_type) as item_type,
                  MIN(group_name) as group_name,
                  
          FROM 
                  `items` 
          WHERE group_name!='UNKNOWN'
          GROUP BY
                  item_no 
      ) i
      on cs.item_no = i.item_no 
      where date BETWEEN '2022-08-21' AND '2022-08-22'
      group by item_no,  i.item_type, i.item_name, i.group_name
      order by sum(quantity) desc
      limit 1000
  </code></pre>
  <p>The query displays top 1000 items by quantity sold and also shows the item's type, name and group_name. How can I modify this query so that I can see the top 10 items for each group_name? So instead of top 1000 items overall, I want to see the top 10 items in each group_name.</p>
  <p>Thank you</p>