Post a Reply
314 views

How to randomize a record?

  1. 5 months ago

    Philip W

    Nov 24 New Mexico, USA

    In my app, I want the user to be able to press a simple button, and by doing so, randomize the database by its row id, then have those contents display their field values on the app's interface. I think I described that correctly.

    I have looked at the Xojo documentation for the Random function and its min/max values. Ideally, I would like the random function's max value to be the recordset's RecordCount value. This would prevent my having to assign a hard number as the max value, and if at some later date, any new record(s) are added then they can be accounted for. But then, I'm puzzled as to how I'm to associate this event with my sql query statement since it is executed prior the recordset call and recordset field associations. Additionally, how do I related the query to the recordset field? In Windows, I set the record position equal to the random integer, then assigned that random value to the row id in my query. For the Mac and Xojo, I haven't got a clue. Any suggestions on how to get started?

    Answered by Greg O

    See post in context

    @Dave S First off. you realize that the RowID may or may not be consecutive numbers? that there may be gaps....
    Meaning that Max(RowID) and RecordCount would not be the same value..

    But if your intention is to grab a random record then here is an idea (this is not "real" code"
    sql="SELECT max(rowID) as maxID from yourtable" do x=random(maxID) SQL="SELECT * from yourtable where rowID=x" if recordcount=1 then exit do loop

    It might be simpler to have sql do this for you. For instance, in SQLite:

    SELECT * FROM table ORDER BY RANDOM();

    If you only want one record, follow that with LIMIT 1.

    EDIT:
    PostgreSQL also random()
    MySQL and MSSQL use rand()

  2. Dave S

    Nov 24 San Diego, California USA

    First off. you realize that the RowID may or may not be consecutive numbers? that there may be gaps....
    Meaning that Max(RowID) and RecordCount would not be the same value..

    But if your intention is to grab a random record then here is an idea (this is not "real" code"

    sql="SELECT max(rowID) as maxID from yourtable"
do
     x=random(maxID)
     SQL="SELECT * from yourtable where rowID=x"
     if recordcount=1 then exit do
loop

  3. Greg O

    Nov 25 Xojo Inc Answer
    Edited 5 months ago

    @Dave S First off. you realize that the RowID may or may not be consecutive numbers? that there may be gaps....
    Meaning that Max(RowID) and RecordCount would not be the same value..

    But if your intention is to grab a random record then here is an idea (this is not "real" code"
    sql="SELECT max(rowID) as maxID from yourtable" do x=random(maxID) SQL="SELECT * from yourtable where rowID=x" if recordcount=1 then exit do loop

    It might be simpler to have sql do this for you. For instance, in SQLite:

    SELECT * FROM table ORDER BY RANDOM();

    If you only want one record, follow that with LIMIT 1.

    EDIT:
    PostgreSQL also random()
    MySQL and MSSQL use rand()

  4. Philip W

    Nov 25 New Mexico, USA

    Thank you for your suggestions. I truly appreciate your feedback.

or Sign Up to reply!