adding DISTINCT keyword to query() with SQLite in Android

Question

the difference between query() and rawQuery() in SQLite when making more complex SQL queries.

for example

i want to use the SQL keyword DISTINCT, so I don't get any duplicates returned from the database. i understand how to use rawQuery() method, that way you can put an actual SQL query statement in the method. in this way i can make a standard SQL statement with rawQuery. it would be easy to add the DISTINCT keyword to any SQL statement when using rawQuery()

however, when using the query() method as shown here in this code, I can't just use regular SQL statements. in this case, how would i make a query with the DISTINCT keyword as part of the query? or something with the same functionality?

            // get info from country table
            public String[] getCountries(int numberOfRows) {

                     String[] columns = new String[]{COUNTRY_NAME};
                     String[] countries = new String[numberOfRows];

                     int counter = 0;

                Cursor cursor = sqLiteDatabase.query(COUNTRY_TABLE, columns, 
                            null, null, null, null, null);

                     if (cursor != null){

                     while(cursor.moveToNext()){
         countries[counter++] = cursor.getString(cursor.getColumnIndex(COUNTRY_NAME));                                   
                    }

            }
                return countries;                        
     }

Solution 1

Instead of the...

public Cursor query(String table, String[] columns, String selection,
                    String[] selectionArgs, String groupBy, String having, 
                    String orderBy)

...method you're using, just use the...

public Cursor query (boolean distinct, String table, String[] columns, 
                     String selection, String[] selectionArgs, String groupBy,
                     String having, String orderBy, String limit)

...overload and set distinct to true.

The Android docs seem a bit hard to direct link, but the doc page describing both is here.

Solution 2

you can use this,

Cursor cursor = db.query(true, YOUR_TABLE_NAME, new String[] { COLUMN1 ,COLUMN2, COLUMN_NAME_3 }, null, null, COLUMN2, null, null, null);

Here first parameter is used to set the DISTINCT value i.e if set to true it will return distinct column value.

and sixth parameter denotes column name which you want to GROUP BY.

Solution 3

You should use another QUERY function with first DISTINCT boolean parameter set to TRUE

public Cursor query (boolean distinct, String table,...)

Comments

[Joachim Isaksson]

The sixth parameter (unless you found another overload) denotes what to GROUP BY, which is very different from a DISTINCT query which finds distinct rows.