Mysql percent based query

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."");

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.

Doubt this can be done properly in a single statement. Personally I would:

1. Calculate the COUNT() for each car type, grab them together in a query.
2. Retrieve both car types separately using sub-queries with LIMIT set to the correct amount and offset based on the percentage desired (so if you want 20 results total, starting at 40, and BMW if 40%, then the limit would be 8 results starting at 16 - they need to be integer values)
3. Using a UNION to combine the results, ORDER BY RAND() to mix them together.

That's only two actual queries, one for the counts, one combined query for the results, you could combine them in a stored procedure if performance is that much of an issue.

You could combine them using a statement prepare/execute from the results - have a look at this method from a possible duplicate question.