# RAND() for MySQL, RANDOM() for SQLite

**URL:** <https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031>\
**Category:** Rails\
**Created:** [March 16, 2009, 9:27pm UTC](https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031 "2009-03-16T21:27:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/977dab/32.png) [@mikec](https://www.ruby-forum.com/u/mikec)\
**Post date:** [March 16, 2009, 9:27pm UTC](https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031/1 "2009-03-16T21:27:29Z")

</div>

I’m trying to pick some random rows from my database; for development  
I use SQLite and for production I use MySQL. However, as the title  
suggests, they both have different ways to get random rows. How can I  
abstract this away?

---

<div class="post-metadata">

**Author:** ![mikec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/977dab/32.png) [@mikec](https://www.ruby-forum.com/u/mikec)\
**Post date:** [March 16, 2009, 9:35pm UTC](https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031/2 "2009-03-16T21:35:43Z")

</div>

> I’m trying to pick some random rows from my database; for development  
> I use SQLite and for production I use MySQL. However, as the title  
> suggests, they both have different ways to get random rows. How can I  
> abstract this away?

Psuedo-ally:

- get the total number of rows via Foo.count.
- Use ruby to get a random number b/n 0 and the total.
- Construct a query using :limit =\> 1 and :offset =\> random\_number.

There’s probably an edge case where you might fall off the end in  
there, but I’ll let you confirm that.

You don’t want to use RAND()/RANDOM() if you can avoid it as mysql  
will fetch _every_ row, assign each a random number sort it, and  
return the first. Not efficient with many rows.

-philip

---

<div class="post-metadata">

**Author:** ![mikec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/977dab/32.png) [@mikec](https://www.ruby-forum.com/u/mikec)\
**Post date:** [March 16, 2009, 9:45pm UTC](https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031/3 "2009-03-16T21:45:13Z")

</div>

Thanks! That did the trick. 🙂 But I’m still curious as to how you  
would generally abstract away something you want to do independent of  
the database.

---

<div class="post-metadata">

**Author:** ![mikec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/977dab/32.png) [@mikec](https://www.ruby-forum.com/u/mikec)\
**Post date:** [March 17, 2009, 5:33am UTC](https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031/4 "2009-03-17T05:33:43Z")

</div>

Philip H. wrote:

> > Thanks! That did the trick. 🙂 But I’m still curious as to how you  
> > would generally abstract away something you want to do independent of  
> > the database.
> 
> By doing exactly what you just did. Or sticking to pure SQL (can’t  
> think of the standard, but there is one) and using nothing else.

Briefly, database independence is AR’s job, not yours. Coding for  
multiple  
databases is a form of “premature complexity”. Wait until your boss is  
actually  
paying you to cover a new database, before wasting time on it. Your  
predictions  
would only cause trouble anyway.

And the great thing about RANDOM is you can seed it with today’s  
date, to  
rotate your content daily…

---

<div class="post-metadata">

**Author:** ![mikec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/977dab/32.png) [@mikec](https://www.ruby-forum.com/u/mikec)\
**Post date:** [March 16, 2009, 9:54pm UTC](https://www.ruby-forum.com/t/rand-for-mysql-random-for-sqlite/163031/5 "2009-03-16T21:54:37Z")

</div>

> Thanks! That did the trick. 🙂 But I’m still curious as to how you  
> would generally abstract away something you want to do independent of  
> the database.

By doing exactly what you just did. Or sticking to pure SQL (can’t  
think of the standard, but there is one) and using nothing else.