Selecting a Random Row from an SQLite Table

Album Cover: Sea Change

"How could this love, ever changing, never change the way I feel?"
Beck / Lonesome Tears

Posted on May 13, 2008 8:32 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

When I originally presented my version of selecting a random row from a MySQL table, a commenter named Elvard set me straight:


Tonight, out of pure curiosity, I learned that selecting a random row from an SQLite table isn't much different:


The only key difference being that MySQL supports the RAND() function, whereas SQLite's is called RANDOM().


Csaba Gabor on July 16, 2008 at 5:00 PM:

In fact, this can be ratcheted up. Suppose that you want to get a certain number of records that exceed a given level. If the level of each row is distinct, then the returned set is unique.

However, if levels have more than one entry, then the highest level falling under the limit should return a random subset. This can be accomplished by:

SELECT * FROM table WHERE level>level_limit ORDER BY level, RANDOM() LIMIT amount;

Csaba Gabor from Vienna


Ceyhun Alyesil on October 01, 2009 at 4:58 PM:

hi thanks for the tip im working on a game project written in python and using sqlite i had to take a random value and with your tip i did it^^


Chris Lasher on April 05, 2010 at 4:51 PM:

I'm in a similar boat as Ceyhun. Thanks for posting this tip.


Gev√£ Schaefer on June 01, 2010 at 10:14 AM:

Thank you


Zach "theY4Kman" Kanzler on November 02, 2010 at 7:02 PM:

Spectacular! This not only helped my current project, but led me to realize how ORDER BY works. Thanks a bunch!


Rohan on December 25, 2010 at 12:06 PM:

Thanks for the tip. I really thought that SQLite has no random functionality. I actually ran into this problem when shifting from a MySQL to SQLite db. It throw a 'No such function: RAND()', and I was almost paralysed!


Coburn on February 24, 2011 at 5:00 PM:

Thanks for the post! Saved me a lot of time working on pulling random rows out of a SQLite DB Table for a PHP IRC Bot project.

Cheers again! :)


Evan Christner on June 25, 2012 at 1:21 PM:

I am using Ruby on Rails 3 (Ruby 1.9.3 + Rails 3.2.5) and sqlite3 with RoR (Ruby on Rails from here on). My "website" is my main question on Stack Overflow about pulling four random rows (all must be different, though) onto a web page using Ruby on Rails. I need to know where I put the syntax "SELECT * FROM table ORDER BY RANDOM() LIMIT 1;" and where I put my code (if it's correct to the syntax) "<%='random()').limit(1).collect { |b| b.content } %>" (it's current location is a .html.erb file)


Gore on January 15, 2014 at 8:05 PM:

Thank you!!!!


Manual blog comments on September 03, 2016 at 1:21 AM:

I have been looking at a couple of your stories and i can state really well done. I will bookmark your online journal


how to get minecraft on September 28, 2016 at 4:00 AM:

This blog is really great. The information here will surely be of some help to me. Thanks!.


how to download movies from netflix on September 28, 2016 at 4:01 AM:

Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for.


twitter follower kaufen on October 08, 2016 at 8:20 AM:

I would like to say that this blog really convinced me to do it! Thanks, very good post. twitter follower kaufen|how to get more views on youtube


Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.


Email Address:



Check this box if you hate spam.