0

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.

philant
  • 34,748
  • 11
  • 69
  • 112
angelina
  • 51
  • 1
  • 1
  • 6
  • Funny how often this question, or similar ones, appears... http://stackoverflow.com/questions/3311039/fetching-rand-rows-without-order-by-rand-in-just-one-query – pascal Jul 23 '10 at 10:42
  • Keep in mind, if you request the random number generator to deliver a different number each call, you lose some randomness. In your example, if the number 1 must not come on the second call, how can it be random? What you want is a set of numbers of which one number is chosen randomly. So you have to create a mechanism to mark previously chosen numbers. And you will run out of options by time and have to reset at a certain point. – bl4ckb0l7 Jul 23 '10 at 10:57

2 Answers2

0

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
  • 377,238
  • 77
  • 533
  • 578
  • 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 Jul 23 '10 at 12:11
  • @Geert Immerzeel: Agreed, just wrote what came to my mind. Yes probably he can resort to post-query randomization too. – Sarfraz Jul 23 '10 at 13:03
0

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
  • 524,688
  • 99
  • 697
  • 795