SQL Random query - 【StackMirror】|mysql|sql|random

In my MySQL Database all the entries have a rating(an Integer). Now I want to have a SQL query that delivers 10 elements randomly. And as you probably guess, an element's chance to be taken should be RatingOfThisElement/AllRatings. How can I do that. (I know how to do it in php but I'd like to have a SQL query because of the performance).

2012-04-04 08:06
by user1200226

Google should help you with this: http://akinas.com/pages/en/blog/mysql_random_row/

2012-04-04 08:10
by briantyler

That's just randomly. But I want the chance to be RatingOfThisElement/AllRatings, not 1/NumElements like in your example - user1200226 2012-04-04 08:46

The point is you need to know how to generate randomness. You need to work out how to apply randomness to your situation - briantyler 2012-04-04 10:06

You can use ORDER BY RAND() LIMIT 10 such as here MySQL: Alternatives to ORDER BY RAND() but there are issues with ORDER BY RAND in MySQL if used in certain ways. It can case huge performance issues and you may be better off doing it in PHP - alternatives to ORDER BY RAND

2012-04-04 08:11
by liquorvicar

That's just randomly,too. But I want the chance to be RatingOfThisElement/AllRatings, not 1/NumElements like in your example - user1200226 2012-04-04 08:47

here is the solution

SELECT * FROM yourtablename ORDER BY RAND() LIMIT 0,10;

2012-04-04 08:19
by IT ppl

If you look at the link I posted that has worse performance than a php query - briantyler 2012-04-04 08:29

but user1200226 didn't tag PHP ; - IT ppl 2012-04-04 11:31