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!

You should avoid use of `rawQuery` whenever possible... always prefer the built in `SQLiteDatabase` query methods over `rawQuery`. — Alex Lockwood, Jun 27 '12 at 04:31

score 64 · Answer 1 · edited Dec 14 '12 at 13:40

64

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.

edited Dec 14 '12 at 13:40

Rory McCrossan

331,213
40
305
339

answered Dec 14 '12 at 13:17

Adam Kowalski

741
5
9

6

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. – Bogdan Zurac Feb 16 '13 at 13:29
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! – Nayanesh Gupte Oct 16 '15 at 09:10
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. – statosdotcom Mar 27 '17 at 01:13

score 50 · Accepted Answer · edited Jun 27 '12 at 04:37

50

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.

edited Jun 27 '12 at 04:37

Rajesh

15,724
7
46
95

answered Jun 27 '12 at 04:27

Harry Joy

58,650
30
162
207

so how to select distinct category, that's for select the whole row right? – smith Jun 27 '12 at 04:31
2

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. – Harry Joy Jun 27 '12 at 04:32

score 7 · Answer 3 · edited Apr 03 '16 at 11:49

7

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";

edited Apr 03 '16 at 11:49

zelanix

3,326
1
25
35

answered Dec 15 '15 at 15:40

Pavan Kunchapu

263
3
9

score 4 · Answer 4 · edited Feb 26 '16 at 21:26

4

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

column3 is a distinct column

edited Feb 26 '16 at 21:26

CubeJockey

2,209
8
24
31

answered Feb 26 '16 at 21:10

Ngosagoogle

41
1

select distinct value in android sqlite

4 Answers4

Linked

Related