Android: ViewBinder & SimpleCursorAdapter SQL woes

Question

I am struggling to get more complicated queries to work with SimpleCursorAdapter and ViewBinder with a ListView. When I was just returning all entries in my table, that was no problem. However, I want to return a list of artist names from my tables in order of name. The big problem concerns the "rowid _id" field which SimplerCursorAdapter/ViewBinder expects.

My code worked fine when I had queries of the form SELECT rowid _id, Artist.NAME etc, but I want to use the DISTINCT keyword to return the unique set if artist names. I can't put "rowid _id" before "DISTINCT Artist.Name" and I can't put it after. What is the solution for this?

The query I want (A) is (shown without the "rowid _id"):

String sQuery = String.format( "SELECT DISTINCT Artist.Name, Artist.ID FROM Artist JOIN Tune ON Artist.ID=Tune.ArtistID AND Tune.Type=%d AND Tune.SubType=%d ORDER BY Artist.Name", nType, nSubtype );

To clarify, this works:

Cursor c = db.rawQuery( "SELECT rowid _id, Name, Rating FROM Tune ORDER BY Name", null );

Whenever I put rowid _id back into query (A), I get "no such column rowid" exceptions:

String sQuery = String.format( "SELECT rowid _id, Artist.Name, Artist.ID FROM Artist JOIN Tune ON Artist.ID=Tune.ArtistID AND Tune.Type=%d AND Tune.SubType=%d ORDER BY Artist.Name", nType, nSubtype );

What am I doing wrong?

EDIT: I don't even understand what the "rowid _id" does anyway - my SQLite Manager (test tool) doesn't like it either when I have a query with a join. It only seems to work on a simple 1 table query.. So if thats the case.. how do I make this query work without it for SimpleCursorAdapter & ViewBinder?

Answer 1

The answer was to forget about using rowid and use my own Artist.ID field instead. This will work as long as I alias the field name to _id which SimpleCursorAdapter expects in column 0.

String sQuery = String.format( "SELECT DISTINCT Artist.ID  _id, Artist.Name  FROM Artist JOIN Tune ON Artist.ID=Tune.ArtistID AND Tune.Type=%d AND Tune.SubType=%d ORDER BY Artist.Name", nType, nSubtype );