how to pick random a entry from a specific group in a SQLite database

Question

From my SQLite database i pulled all of the entries that have "Liquor" in the KEY_ALCOHOL column, how do i go about picking a random entry from the selected group?

Here is how i am getting the group:

public Cursor getAlcohol(String alcohol) throws SQLException 
{
    Cursor mCursor =
            myDataBase.query(true, DB_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_ALCOHOL, 
                    KEY_TYPE,
                    KEY_BRAND,
                    KEY_PRICE
                    }, 
                    KEY_ALCOHOL + "=?", 
                    new String[] { alcohol },
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

This returns about 60 different entries and i need to choose just one at random.

EDIT

This is the second attempt on the database helper .java

public Cursor getTest(String test) throws SQLException 
{
    Cursor mCursor =
            myDataBase.query(true, DB_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_ALCOHOL, 
                    KEY_TYPE,
                    KEY_BRAND,
                    KEY_PRICE
                    }, 
                    KEY_ALCOHOL + "=?", 
                    new String[] { test },
                    null, 
                    null, 
                    "RANDOM()", 
                    "LIMIT 1");
    return mCursor;
}

This is how i am calling it in the main activity:

myDbHelper.openDataBase();
    Cursor c = myDbHelper.getTest("Liquor");
    if (c.moveToFirst())
    {
        do {          
            DisplayTitle(c);
        } while (c.moveToNext());
    }
    myDbHelper.close();

}

public void DisplayTitle(Cursor c)
{
    Toast.makeText(this, 
            "id: " + c.getString(0) + "\n" +
            "ALCOHOL: " + c.getString(1) + "\n" +
            "TYPE: " + c.getString(2) + "\n" +
            "BRAND:  " + c.getString(3) + "\n" +
            "PRICE:  " + c.getString(4),
            Toast.LENGTH_LONG).show();        
}

possible duplicate of [Select random row from an sqlite table](http://stackoverflow.com/questions/2279706/select-random-row-from-an-sqlite-table) — Graham Borland, Sep 13 '12 at 14:33
what's the error, what have you tried, do you know about random()? and about cursor.moveTo ? — njzk2, Sep 13 '12 at 15:18

score 1 · Accepted Answer · answered Sep 13 '12 at 14:27

1

You need to add

ORDER BY RANDOM() LIMIT 1;

to your query. So try this (untested):

        myDataBase.query(true, DB_TABLE, new String[] {
                KEY_ROWID,
                KEY_ALCOHOL, 
                KEY_TYPE,
                KEY_BRAND,
                KEY_PRICE
                }, 
                KEY_ALCOHOL + "=?", 
                new String[] { alcohol },
                null, 
                null, 
                "RANDOM()", 
                "LIMIT 1");

answered Sep 13 '12 at 14:27

Graham Borland

60,055
21
138
179

what exactly is this supposed to return? a random selection from all the entries that have to same value in the KEY_ALCOHOL column ? – Mr. Pivirotto Sep 13 '12 at 14:36
A random entry from the set of results your query would otherwise return. – Graham Borland Sep 13 '12 at 14:37
I edited my main post, i tried to implement your method but its crashing and telling me i have an "Invalid LIMIT"? – Mr. Pivirotto Sep 13 '12 at 14:47
Just use `rawQuery()`, it's far easier than trying to figure out all the arguments to the `query()` method. – Graham Borland Sep 13 '12 at 14:48
could you show me an example? I am very new to the whole SQLite database, im still trying to get a grasp on it and i have no idea how to rawQuery() – Mr. Pivirotto Sep 13 '12 at 14:50
no ! don't use rawQuery ! you are supposed to use rawQuery only if you cannot acheive what you are trying to do with other kind of queries. – njzk2 Sep 13 '12 at 15:19

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

Get the size with mCursor.getCount(), randomly pick a number within 0 and count (see How do I generate random integers within a specific range in Java?), then move the mCursor with mCursor.moveToPosition(randomInt), get the row, I'd suggest.

edited May 23 '17 at 11:55

Community

1
1

answered Sep 13 '12 at 14:24

fweigl

21,278
20
114
205

how to pick random a entry from a specific group in a SQLite database

EDIT

2 Answers2