title: performance of mysql query result using max, group by, temporary table and random row selection
body: <p>I am selecting unique productid w.r.t sizeid from below table, but i need to select random row when prices are same.</p>
  
  <pre><code>aff_id,  wl_product_id, wl_size_id,  price
  1           40             10        57
  3           41             11        65
  4           41             11        67
  1           41             11        67
  </code></pre>
  
  <p>I am expecting below result if price are same, will result random aff_id(4 or 1 in above example).</p>
  
  <pre><code> aff_id,  wl_product_id, wl_size_id,  price,   random_number
      1           40         10        57        37.5708656809953
      4(random)   41         11        67        88.2194444427453
  </code></pre>
  
  <p><strong>Below query results same as above. but is good with respect to performance as i am using temporary table.</strong> </p>
  
  <pre><code>SELECT * FROM (
     SELECT ap1.aff_id,ap1.wl_product_id,ap1.wl_size_id, ap1.price,(ap1.price*RAND())AS random_number 
     FROM affiliate_product ap1
     INNER JOIN 
     (SELECT wl_product_id, MAX(price) AS price FROM affiliate_product  WHERE wl_product_id&gt;0 GROUP BY wl_product_id,wl_size_id) ap2
     ON (ap1.wl_product_id = ap2.wl_product_id AND ap1.price = ap2.price) ORDER BY wl_product_id,random_number
  )AS temp_tbl GROUP BY wl_product_id,wl_size_id
  </code></pre>