How to select distinct values from one column in table?

Question

I am developing a restaurant menu app in Android. My app has one database table which has the following columns:

id (primary key)
category
item name

The category column shows the category of item such as veg, non veg, snacks etc. It has duplicate values and I want to select only distinct values from this column. I have tried the following but it is not working if anyone can provide a solution:

String query = "SELECT DISTINCT category FROM todo";

Cursor  cursor = database.rawQuery(query,null);
if (cursor != null) {
    cursor.moveToFirst();
}

return cursor;

i am not getting any sqlite errors, but app stops running, debuger shows error source not found. Curently i am not using any logging in app — sagar, Sep 13 '11 at 14:11
You should use logcat to debug your application. It will give us more information about your problem.. — Jeremy D, Sep 13 '11 at 14:34

score 12 · Answer 1 · answered Jun 21 '12 at 11:34

You can also use this specific query-Method of the SQLiteDatabase class that takes a boolean value to determine whether you want distinct values or not:

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

Link to Android Reference

This way you don't have to use rawQuery at all.

score 6 · Answer 2 · edited Feb 29 '12 at 09:59

6

Try:

"SELECT DISTINCT category as _id, category FROM todo"

This worked for me in the same situation.

edited Feb 29 '12 at 09:59

Aliaksei Kliuchnikau

13,589
4
59
72

answered Feb 29 '12 at 08:51

StarSight

69
1
2

3

Could you explain what this means? Why do you need to replace "FROM todo" with "as _id, category FROM todo"? – Greener Nov 12 '12 at 04:57
This answer should really include more details than it worked for me. – Shawn Dec 15 '20 at 02:02

score 1 · Answer 3 · answered Apr 07 '14 at 13:40

This is how i make distinct, also to get categories

// Get Categories
public Cursor getCategories() {

    Cursor c = db.rawQuery("SELECT DISTINCT " + KEY_ITEM_CAT + " as " + KEY_ITEM_ID
            + ", " + KEY_ITEM_CAT + " FROM " + ITEMS_TABLE_NAME, null);

    if (c != null) {
        c.moveToFirst();
    }

    return c;
}

score 0 · Answer 4 · edited May 23 '17 at 12:08

0

I was searching for the same question, and I found something which is direct to the solution in the most simplest way! Check out this answer:

https://stackoverflow.com/a/13879436/4072073

edited May 23 '17 at 12:08

Community

1
1

answered Oct 07 '14 at 18:08

Tom R.

71
1
1
7

How to select distinct values from one column in table?

4 Answers4

Linked