Random picking of DB records

[Penfound]

I have a questionnaire app that work mostly but I have a problem.

I fetch the total number of records in the DB and then select a record ID by using the random number generator. However, I wish to avoid presenting the same question twice so I included a field to hold the info about whether it had already been used.

The problem I now have is that, if the rnd function picks a valid number, that record might not be available in the DB and therefore everything crashes.

So, how can I pick a random number from a non-sequential number list?

Cheers
Penfound

 

[mc73]

One way is to use a cursor and a loop. In case that the selected random ID cannot be found, you can loop again and search for another random id.

Symbolic:

do while flagFound=false
pick a Random ID
Select this id's fields from your db
if cursor size=1 then flagFound=true
loop

 

[Penfound]

Unfortunately I have already tried this and the app crashes when a random number is selected that corresponds to a record id that does not exist in the record set before it can check whether the record is available.

I'm trying at the moment to scan the records into an array before each question is presented then pick a random array index - just seems a little clunky.

But thanks for your input

 

[mc73]

Used a cursor or a singleResult query?

 

[mc73]

You can also try this:

SELECT * FROM yourTable ORDER BY RANDOM() LIMIT 1

 

[Penfound]

That is really neat! Thank you all I need to do now is put the bits in to stop it going past the beginning or end of the db.

This piece of info really outght to be sticky. I've seen many queries of a similar nature and most end up with an array.

Brilliant:sign0142:

 

[BrutalSnyper]

You can also try this:

SELECT * FROM yourTable ORDER BY RANDOM() LIMIT 1

When I use this code, it returns:

android.database.sqlite.SQLiteCursor@4156c808

Any ideas?

FIXED: Fixed by using ExecQuerySingleResult rather than ExecQuery.

 

[klaus]

I have been using a List with the ID's of the records.
When I read the DB I fill a table and a List with the IDs.
That way I know what ID is in what row.
You could use thr same for your random search.
When I remove a record from the DB and table I remove also the given entry in the List.

Best regards.