Dear all, I want to pick a random number from a database say 1, when I query again say 10 times I'm querying it should return all different random numbers.
A:
Use ORDER BY RAND()
If you want to get just one random record, your query should look like:
SELECT * FROM table ORDER BY RAND() LIMIT 1
Sarfraz
2010-07-23 10:40:54
That does not guarantee that when you run this query 10 times it returns all different random numbers. When there are a lot of items in the table you have a pretty good chance that the 10 items you pick will be different, no more, no less...
Geert Immerzeel
2010-07-23 12:11:07
@Geert Immerzeel: Agreed, just wrote what came to my mind. Yes probably he can resort to post-query randomization too.
Sarfraz
2010-07-23 13:03:26
A:
If you only want a random number, don't use a database, use a random number generator. If you don't want repeats, simply keep track of the random numbers you've seen before and, if you pick one again, increment the new number by one until you reach a number you haven't seen yet.
If you want, say 10, random records from the database, then use @sAc's solution, but get them all at the same time. That will ensure that you don't have repeats in your selection. If you must select them one at a time, use the same technique as for the random number and keep track of the records you've seen before. Don't use the LIMIT directive, but simply select the first record you haven't seen on each iteration.
tvanfosson
2010-07-23 10:52:36