Selecting a Random Row from an SQLite Table

Album Cover: Plans

"And it came to me then that every plan is a tiny prayer to Father Time."
Death Cab / What Sarah Said

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

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

Permalink

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!.

Permalink

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.

Permalink

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

Permalink

السعودية on March 26, 2017 at 1:34 AM:

Thanks for the article!

Permalink

mens designer laptop bags leather on April 04, 2017 at 5:35 AM:

Offering wide collection of men designer bags made from high quality materials. Our bags made in Italy and we ship it for free to you.

Permalink

اسنان on April 09, 2017 at 10:28 AM:

Effectively, the article is really the best theme on this registry related issue. I fit in with your decisions and will excitedly anticipate your next overhauls.

Permalink

seo backlinks service on April 19, 2017 at 3:10 AM:

Thank you very much for keep this information.†Good ideas! I learn many here.

Permalink

the best carding forum on April 23, 2017 at 8:29 AM:

I would like to thank you for the efforts you have made in writing this article.

Permalink

csgo matches betting on May 02, 2017 at 12:48 PM:

Bet on an outcome of events in Counter Strike:Global Offensive matches and earn coins for purchasing of skins in the Store.

Permalink

ysyprawp on May 15, 2017 at 5:23 AM:
Agencia de marketing digital en Monterrey on May 19, 2017 at 10:15 AM:

I am hoping the same best work from you in the future as well..

Permalink

versace 1969 handbags on June 02, 2017 at 4:18 AM:

I am happy to find your distinguished way of writing the post.

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.