Mysql percent based query

Question

Is this kind of mysql query possible?

  SELECT power 
    FROM ".$table."
   WHERE category IN ('convertible') 
     AND type = bwm40% 
     AND type = audi60% 
ORDER BY RAND()

Would go something like this: from all the cars, select the power of the ones that are convertible, but 40% of the selection would be bmw's and the other 60% audi's.

Can this be done with mysql?

Can't seem to make it work with the ideea bellow, gives me an error, here is how I tried it:

    $result = mysql_query("
SELECT power, torque FROM ".$table." 
WHERE category IN ('convertible') 
ORDER BY (case type when 'bmw' then 0.4 when 'audi' then 0.6) * RAND() DESC 
LIMIT ".$offset.", ".$rowsperpage."");

Answer 1

You could try adjusting the randomness using a CASE:

SELECT power
FROM table
WHERE category IN ('convertible')
  AND type IN ('bwm', 'audi')
ORDER BY (case type when 'bwm' then Wbwm when 'audi' then Waudi) * RAND() DESC

Where Wbmw and Waudi are weighting factors. Then you'd add a LIMIT clause to chop off the results at your desired size. That won't guarantee your desired proportions but it might be good enough for your purposes.

You'd want to play with the weighting factors (Wbmw and Waudi above) a bit to get the results you want. The weighting factors would depend on frequencies of bwm and audi in your database so 0.2 and 0.8, for example, might work better. As Chris notes in the comments, 0.4 and 0.6 would only work if you have a 50/50 split between BMW and Audi. Putting the weights in a separate table would make this approach easier to maintain and the SQL would be prettier.