Does sqlite in webkit support a RANDOM() function?

Question

I'm coding a Phonegap based app for iOS, and am trying to return 10 random rows from my Safari webkit database via Javascript. My query is:

queryString = "SELECT * FROM SBA_TABLE
 WHERE (cat_gastrointestinal = 1)
 AND (answered_correctly = 0 OR answered_correctly = 1 OR answered_correctly = 2)
 ORDER BY RANDOM() LIMIT 10";

tx.executeSql(queryString, [], querySuccess5, errorCB);

This works if I omit the 'ORDER BY RANDOM()' statement which leads me to believe this is not supported. Is there a RANDOM method I could use, or do I have to generate 10 random numbers and make 10 database calls?! Thanks, Nick

Normally SQLite has a [random](http://www.sqlite.org/lang_corefunc.html) function, and the syntax is correct... What happens when you use random? Is an error thrown or does it not randomize, or return no rows? — MPelletier, Apr 07 '11 at 23:15
It returns no rows. I decided to pull the results into an array & shuffle it with a random function as described here: http://stackoverflow.com/questions/4027910/how-to-randomize-subset-of-array-in-javascript http://jsfromhell.com/array/shuffle But if anyone knows of a random SQL function this would be much cleaner. — hairyllama, Apr 08 '11 at 07:20
What version of SQLite do you have? There's a page here on returning the version: http://www.sqlite.org/c3ref/libversion.html — MPelletier, Apr 08 '11 at 10:09
Hi MPelletier, thanks for your help but ultimately my application is being deployed to the iPhone, so I'm relying on the version that Apple deployed/implemented. — hairyllama, Apr 08 '11 at 18:48

score 2 · Answer 1 · answered Nov 08 '12 at 02:05

2

I just had the same problem, I solved it with such workaround:

seed = (Math.random() + 1) * 1111111;
queryString = "SELECT * FROM SBA_TABLE
 WHERE (cat_gastrointestinal = 1)
 AND (answered_correctly = 0 OR answered_correctly = 1 OR answered_correctly = 2)
 ORDER BY ROWID * ? % 10000 LIMIT 10";

tx.executeSql(queryString, [ seed ], querySuccess5, errorCB);

Using the Javascript Math.random() function and a modulo on the table ROWID in the query.

answered Nov 08 '12 at 02:05

Emmanuel Paris

200
2
7

great, but this will get a consecutive set of 10 entries at a random location. Can you pass 10 random numbers for totally random entries? – Tarek Eldeeb Dec 31 '15 at 20:52

score 0 · Accepted Answer · answered Apr 08 '11 at 19:00

0

Tentative workaround, in case ORDER BY RANDOM() specifically isn't functional on that version:

"SELECT *, RANDOM() AS rnd FROM SBA_TABLE
WHERE (cat_gastrointestinal = 1)
AND (answered_correctly = 0 OR answered_correctly = 1 OR answered_correctly = 2)
ORDER BY rnd LIMIT 10";

answered Apr 08 '11 at 19:00

MPelletier

16,256
15
86
137

1

No, this doesn't work. Webkit forbids the usage of the random() function. See also http://stackoverflow.com/questions/3500842 – Ignitor Jul 12 '12 at 14:06
@Ignitor thanks. The shuffle approach on that question is the best approach, then. In fact, cleaner even if it's a bit more work. – MPelletier Jul 12 '12 at 17:32

Does sqlite in webkit support a RANDOM() function?

2 Answers2