greatest n per group needed in compound Mysql join sql -

After

I am going to join 3 tables with this query

  DISTINCT a.number, Select c. Quantity, include `c.retail_price catalog.product_variation from catalog.product a.`` b` on a.id = b.product_id catalog.price_regular` join c` at b.id = c.product_variation_id where between 5 and 6 C.retail_price and a.number by a.status_id = 1 command, c.retail_price DESC  

and I get this result set

  number | Volume | Retail_price - ------------------- 1007 | 288 | 5.750 1007 | 48 | 5.510 1007 | 576 | 5.460 1007 | 96 | 5.240 1007 | 576 | 5.230 1007 | 144 | 5.120 1006 | 200 | 5.760 1006 | 100 | 5.550 1006 | 200 | 5.040 1006 | 500 5.010  

What do I need only to be the most spectacular retail_price quantity column and the highest value is also in line with / Code>. So my result set looks like this

  number | Volume | Retail_pris --------------------- 1006 | 500 5.010 1007 | 576 | 5.460  

I have received some posts but no one was helpful in joining more than one of the tables.

This is a simple group query

p >

  select a.number, in the form of maximum quantity (c.quantity), include `A` catalog.product_variation from the maximum catalog.product` as the cost (c.retail_price) b` Include a.id = b .product_id catalog.price_regular at `c` b.id = c.product_variation_id where between 5 and 6 and a.number a.status_id = 1 group between c.retail_price;