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
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^^
I'm in a similar boat as Ceyhun. Thanks for posting this tip.
Thank you
Spectacular! This not only helped my current project, but led me to realize how ORDER BY works. Thanks a bunch!
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!
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! :)
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)
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.
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