Selecting a Random Row from an SQLite Table

Album Cover: Icky Thump

"I'm gettin' hard on myself, sittin' in my easy chair."
White Stripes / 300 M.P.H. Torrential Outpour Blues

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


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

Thanks for the article!


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.


اسنان 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.


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.


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.


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.


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


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

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


shadoegang on June 30, 2017 at 12:54 PM:

Iíve been thinking about writing a very comparable post over the last couple of weeks,


دانلود قسمت 14 عاش on July 18, 2017 at 1:32 AM:

دانلود قسمت 14 چهاردهم سریال عاشقانه با لینک مستقیم, دانلود رایگان سریال عاشقانه, دانلود سریال جدید عاشقانه ایرانی دانلود سریال عاشقانه با لینک مستقیم, دانلود فیلم های جدید و برتر, دانلود رایگان فیلم و سریال با لینک مستقیم


دانلود قسمت 14 عاش on July 18, 2017 at 1:33 AM:

دانلود قسمت 14 چهاردهم سریال عاشقانه با لینک مستقیم, دانلود رایگان سریال عاشقانه, دانلود سریال جدید عاشقانه ایرانی دانلود سریال عاشقانه با لینک مستقیم, دانلود فیلم های جدید و برتر, دانلود رایگان فیلم و سریال با لینک مستقیم


marathi actress on July 27, 2017 at 12:54 PM:

Great tips and very easy to understand. This will definitely be very useful for me when I get a chance to start my blog.


buy designer handbags online uk on July 31, 2017 at 1:21 AM:

This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this..


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.