# Sql Rand() returns same result

**URL:** <https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664>\
**Category:** Rails\
**Created:** [May 23, 2009, 7:44pm UTC](https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664 "2009-05-23T19:44:08Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jgjgjg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/22d042/32.png) [@jgjgjg](https://www.ruby-forum.com/u/jgjgjg)\
**Post date:** [May 23, 2009, 7:44pm UTC](https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664/1 "2009-05-23T19:44:08Z")

</div>

I have a method that calls Model.find(:first, :order =\> “RAND()”)  
twice. I want it to return two different results but it always returns  
the same thing twice. Is there some kind of sql caching at work here?  
If so, can I disable for this method?

thanks!

Jason

---

<div class="post-metadata">

**Author:** ![jgjgjg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/22d042/32.png) [@jgjgjg](https://www.ruby-forum.com/u/jgjgjg)\
**Post date:** [May 24, 2009, 7:07am UTC](https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664/2 "2009-05-24T07:07:11Z")

</div>

First of all, I’m assuming you are calling rand() (lowercase).  
rand() returns a value less than between 0 and 1.  
Using this as the call to :order tells the database to use the column  
reference (rather than a name).  
In MySQL, if you pass a non-integer column value, it gets ignored.  
(Not sure how other databases handle it).

I don’t really understand what you are trying to randomize in your  
order parameter.  
You should probably get the count of the records in your table and  
generate a random number between 1 and that count. Then use offset to  
select that record.

---

<div class="post-metadata">

**Author:** ![jgjgjg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/22d042/32.png) [@jgjgjg](https://www.ruby-forum.com/u/jgjgjg)\
**Post date:** [May 24, 2009, 5:12pm UTC](https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664/3 "2009-05-24T17:12:06Z")

</div>

On Sat, May 23, 2009 at 10:06 PM, E. Litwin [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

Speaking strictly from a MySQL perspective –

> First of all, I’m assuming you are calling rand() (lowercase).

Case isn’t significant.

> I don’t really understand what you are trying to randomize in your  
> order parameter.  
> You should probably get the count of the records in your table and  
> generate a random number between 1 and that count. Then use offset to

? The syntax below works just fine to return a random instance of  
Model.

> > I have a method that calls Model.find(:first, :order =\> “RAND()”)  
> > twice. I want it to return two different results but it always returns  
> > the same thing twice.

What it _doesn’t_ do is guarantee that any two randomly selected  
instances won’t be the same 🙂

Quick example:

> > (1…20).each do  
> > ?\> @up = UserProfile.find(:first, :order =\> “rand()”)  
> > puts @up.id  
> > end  
> > 8  
> > 8  
> > 11  
> > 1  
> > 5  
> > 1  
> > 13  
> > 13  
> > 2  
> > 6  
> > 17  
> > 5  
> > 4  
> > 9  
> > 6  
> > 17  
> > 8  
> > 10  
> > 7  
> > 5  
> > =\> 1…20

If the two really need to be different, you’ll have to address that with  
application logic.

## FWIW,

Hassan S. ------------------------ removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![jgjgjg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/22d042/32.png) [@jgjgjg](https://www.ruby-forum.com/u/jgjgjg)\
**Post date:** [May 25, 2009, 5:17am UTC](https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664/4 "2009-05-25T05:17:19Z")

</div>

My bad - I didn’t notice the quotes around “rand()” in the first  
post.

I thought a random number was being passed to the database rather than  
a function.

On May 24, 8:10 am, Hassan S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![jgjgjg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/22d042/32.png) [@jgjgjg](https://www.ruby-forum.com/u/jgjgjg)\
**Post date:** [May 24, 2009, 7:27pm UTC](https://www.ruby-forum.com/t/sql-rand-returns-same-result/168664/5 "2009-05-24T19:27:03Z")

</div>

You may be getting stuck in the query cache. Try this:

Model.uncached { Model.find(:first, :order =\> “RAND()”) }

Which will shut off query caching for that call.

–Matt J.