Search code examples
javaandroidandroid-sqlite

How to not use LIMIT when using DISTINCT with SQLiteDatabase query method?

I want to use the SQL SELECT DISTINCT book FROM bible ORDER BY book; using the SQliteDatabase query method, I try :-

Cursor csr = mDB.query(true,TABLE_BIBLE,new String[]{COL_BIBLE_BOOK},whereclause,whereargs,null,null,COL_BIBLE_BOOK,"-999");

Two of the 4 query have boolean true for DISTINCT, both have to have LIMIT parameter. SQLite says negative for no limit but doing this gives error like

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.bible/com.example.bible.MainActivity}: java.lang.IllegalArgumentException: invalid LIMIT clauses:-999

I know you can do with rawQuery but it not recommended to use it from Android Devloper Guide. So want to use query.

I know also you can do using large number that not good though it can confuse.

Solution

  • You can use null (like for most of the other parameters (except the 1st (table name)) :-

    Cursor csr = mDB.query(true,TABLE_BIBLE,new String[]{COL_BIBLE_BOOK},whereclause,whereargs,null,null,COL_BIBLE_BOOK,null);

    Alternately you can include DISTINCT (in this case) along with the column name e.g. :-

    Cursor csr = mDB.query(TABLE_BIBLE,new String[]{"DISTINCT " + COL_BIBLE_BOOK},whereclause,whereargs,null,null,COL_BIBLE_BOOK);

    The first would likely be the preferable.