Android database query

Question

I have a database table with 3 columns: _id, name, groupName

It looks similar to this:

1 Tom group1

2 Sam group1

3 Ted group2

4 Jerry group3

5 Me group3

I want to query the database table for groupName. I have tried this:

public Cursor getAllGroups() 
{
      return db.query(DATABASE_TABLE_1, new String[] {KEY_GROUP}, 
            null, null, null, null, null);
}

And this returns a cursor along: (group1, group1, group2, group3, group3).

Is there a way to get only unique groups? (So I would end up with a cursor along: (group1, group2, group3)

Thanks in advance.

Answer 1

I think you need use raw SQL query with DISTINCT keyword. Rough code would be something like:

db.rawQuery("select distinct groupName from "+DATABASE_TABLE_1, null);

Here is SQL tutorial about the keyword http://www.sql-tutorial.com/sql-distinct-sql-tutorial/

Answer 2

Try something like

db.rawQuery("select distinct column1, column2, column3 from tablename", null);

Answer 3

Wow. I figured it out. I am just posting this to help others who may have the same problem as me.

Steps for successful query building (for Android dev):

1) Download and launch "RazorSQL"

2) Connect to your database (follow on-screen instructions)

3) Click "DB Tools" -> "Query Builder" -> "Your database table name"

4) Graphically build your query (play around with everything, then click "Generate SQL")

5) Copy the SQL statement, and paste it in the RazorSQL editor window

6) Click the green arrow ("->"). Your query is now performed on your database and the results are displayed!

7) Copy the SQL statement, go to "Eclipse" (Android dev environment) and paste it as a string into a rawQuery() function

Cool!

So, the correct answer to my question is this:

db.rawQuery("SELECT _id, groupName FROM titles GROUP BY groupName", null);

--> I discovered the "GROUP BY" by playing around in the graphical SQL builder

Have fun with SQL Queries!