select distinct value in android sqlite

Question

I try to run the following raw query in android, it seems not work

String query ="SELECT DISTINCT category FROM event";
Cursor  cursor = mDb.rawQuery(query, null);
if (cursor != null) {
     cursor.moveToFirst();
}
return cursor; 

so I decide to use the query() method in android which are something like

Cursor mCursor = mDb.query(EVENT_TABLE, new String[] {KEY_ROWID, KEY_CAT}, null, null,null,null, null)

Can anyone show me how to select the distinct category for using query() instead of rawquery please, any help will be greatly appreciated!

Fatal

You should avoid use of rawQuery whenever possible... always prefer the built in SQLiteDatabase query methods over rawQuery. – Senecal

Answer 1

You can use this method:

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

Here first argument specifies whether to use distinct or not.

Tasset

so how to select distinct category, that's for select the whole row right? – Fatal

Pass only category in columns[], as you just need that column in result as shown in your row query, and you will get distinct categories. – Tasset

Answer 2

But you MUST remember to send argument in GROUPBY (NOT NULL send).

You must give column name for distinct.

Example:

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

true - distinct TRUE

COLUMN_NAME_2 - name column what you have be DISTINCT.

That's works for me fine.

Savour

EPIC WIN ! You sir, ARE AWESOME ! I've been looking for hours for this problem, thanks a lot for your input. You really made my day. – Faint

Thanks a ton! this is what I was looking for. specially to deal with contentprovider along with cursorloader. I did a small hack and this worked! – Coffle

Yes, have to say thank you too. Was searching and trying many suggestions, only yours worked for me. Thank you for sharing. Keep it up. – Coruscation

Answer 3

There are multiple ways, as already green ticked. But if somebody is looking to get it via raw query then here you go:

String query = "SELECT DISTINCT(category) FROM event";

Tailstock

Answer 4

Cursor res=db.rawQuery("select column1 column2...  Distinct column3 from "+Table_name, null);

column3 is a distinct column

Canoodle