Selecting a Random Row from an SQLite Table

Album Cover: Abbey Road

"She's killer-diller when she's dressed to the hilt."
The Beatles / Polythene Pam

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:

SELECT * FROM table ORDER BY RAND () LIMIT 1;

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

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

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

Comments

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

Permalink

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^^

Permalink

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

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

Permalink

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

Thank you

Permalink

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!

Permalink

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!

Permalink

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! :)

Permalink

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) "<%= Bullet.select(:content).order('random()').limit(1).collect { |b| b.content } %>" (it's current location is a .html.erb file)

Permalink

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

Thank you!!!!

Permalink

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.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.