Android's SimpleCursorAdapter with queries using DISTINCT

Question

Here's an interesting question that I'm shocked hasn't been asked more often on the internet. Android's CursorAdapters are extremely useful once you get a ContentProvider up and running and learn how to use it, but they are limited due to their requirement on having the _id field as part of their query (an error is thrown without it). Here's why:

My specific problem is that I have two spinners: One spinner should contain unique "category" names from the database, and the other should populate with all the database entries from the selected "category" (category being the column name, here). This seems like a pretty simple setup that many programs might use, no? Trying to implement that first spinner is where I've run into problems.

Here's the query that I would like for that first spinner:

SELECT DISTINCT category FROM table;

Making this query throws an error on CursorAdapter because the _id column is required as part of the query. Adding the _id column to the projection naturally returns every row of the table, since you're now asking for distinct id's as well, and every id is distinct (by definition). Obviously I would rather see only one entry per category name.

I've already implemented a work around, which is to simply make the query above and then copy the results into an ArrayAdapter. My reason for this post is to see if there was a more elegant solution to this odd little problem and start a discussion on what I could be doing better. Alternate implementation suggestions, such as using different kinds of controls or adapters, are very welcome.

Answer 1

Here's the query I ended up with:

SELECT _id, category FROM table_name GROUP BY category;

I used the rawQuery() function on an SQLiteDatabase object to carry this out. The "GROUP BY" piece was the key towards getting the right results, so thanks to user Sagar for pointing me in the right direction.

Do consider user Al Sutton's answer to this question as well, as it may be a more elegant solution to this problem.

Thanks everyone!

Answer 2

I'd suggest having a separate table with just _id & category in it which contains one row per unique category. Your data rows can then replace their category field with the _id from the category table.

This has the added advantage you can change the category in the categories table and it will show up in all entries in that category.

Answer 3

SELECT DISTINCT category,_id FROM table GROUP BY category;

I think this should give you what you are looking for. The results from this will be the category, and the first _id for that category. You can ignore the second column (_id).