SQL Select Random Row

Question

I'm having trouble selecting a random row from an sqlite database, this works to select all rows meeting a criteria:

tx.executeSql('SELECT * FROM games WHERE genre="fps" AND decade=90', [], renderResultsTest);

If I then try to retrieve just one of these randomly it wont work:

tx.executeSql('SELECT * FROM games WHERE genre="fps" AND decade=90 ORDER BY RANDOM() LIMIT 1', [], renderResultsTest);

I have a workaround where I use the first statement and loop through the results putting the row ID's into an array and then select a random ID from the array. This works fine but I'd prefer to do it directly in one statement.

I've seen this question asked before on here and googled it myself but it seems the order by random and limit to one result should work, is my syntax wrong or something else causing the problem?

Any advice would be most welcome,

Thanks

its RANDOM in sqlite apparently http://www.sqlite.org/lang_corefunc.html thanks though, tried it anyway, no difference — mao, Oct 26 '11 at 18:14
If anyone else reads this it seems random()wont work with html5 local storage, trying in chrome and safari. Thanks to jules for the help. — mao, Oct 26 '11 at 19:14

Jules · Answer 1 · 2011-10-26T19:17:55.417

4

Usually you're supposed to use the following query in sqlite to select a single random row:

SELECT * FROM games WHERE genre="fps" AND decade=90 ORDER BY RANDOM() LIMIT 1;

So doing this through JQuery it'd be:

tx.executeSql('SELECT * FROM games WHERE genre="fps" AND decade=90 ORDER BY RANDOM() LIMIT 1', [], renderResultsTest);

The problem is though that the embedded version of sqlite in chrome/firefox does not support the RANDOM() function. If you'd try to substitute RANDOM() with id the query works!

So the only way to solve this issue is to retrieve all the records, count the rows and generate a random number between 0 and the number of rows - 1. Then you can select that specific row out of the dataset and use that one.

tx.executeSql('SELECT * FROM games WHERE genre = "FPS" AND decade = 90 ORDER BY RANDOM() LIMIT 1', [], function (tx, result) { 
     var len = result.rows.length; 

     //generate random number
     var i = Math.floor(Math.random() * len);
     //get row
     var row = result.rows.item(i); 
});

edited Oct 26 '11 at 19:17

answered Oct 26 '11 at 18:00

Jules

7,148
6
26
50

sorry I typed that instead of copy paste, the single quote problem isnt actually in my code just a typo I'll edit the op. Still doesn't work. – mao Oct 26 '11 at 18:06
I just tested this in my own Sqlite. And the query works fine. What is it that is going wrong? Not returning anything? Do you have any game there with that genre and decade? – Jules Oct 26 '11 at 18:18
yup several, would you mind telling me how you are testing it so I can check I'm doing it correctly – mao Oct 26 '11 at 18:24
I just open my sqlite console so I can manually write and execute queries on my sqlite database, so I wrote a query similar to yours to match a table that I have in my db. And it works. Try doing that too. – Jules Oct 26 '11 at 18:29
ahh sorry I should have said I'm doing everything with html5 creating sqlitedb in localstorage, the query does nothing for me in chrome or safari – mao Oct 26 '11 at 18:32
What happens if you omit the `ORDER BY RANDOM() LIMIT 1`? Do you retrieve any records having genre fps and decade 90? And what is it exactly that is not working? Because again, the query is correct, I think the mistake lies somewhere else. – Jules Oct 26 '11 at 18:37
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4537/discussion-between-jules-and-mao) – Jules Oct 26 '11 at 18:40
well the renderResultsTest Function alerts the id of the first row which has genre fps and decade 90, works fine with the first query. Does nothing with the second query, Pretty much like this thread, maybe its a problem with localstorage http://stackoverflow.com/questions/4881232/sqlite-random-not-working-in-browser – mao Oct 26 '11 at 18:41

SQL Select Random Row

1 Answers1