Distinct values in Android ExpandableListView

I'm having trouble with an ExpandableListView. What I want is for each group to be from column "bond", which is non-unique, but I want the groups to be unique (i.e. each value of "bond" should only have one group). Each value in the group has data in other columns which should be displayed. The problem is that it seems I can either use the SQL DISTINCT operator with one column (and then getChildrenCursor() throws an IllegalStateException), or not use the DISTINCT operator and have each group duplicated.

Can anyone suggest a fix for this?

public void onCreate(Bundle saved) {

    super.onCreate(saved);

    DatabaseHelper dh = new DatabaseHelper(this);
    dh.openDataBase();
    db = dh.getReadableDatabase();

    String query = "SELECT DISTINCT bond FROM spectro";

    Cursor c = db.rawQuery(query, null); //throws exception when group opened
    //Cursor c = db.query("spectro", new String[] { "_id", "name", "bond", ir },
        //null, null, null, null, null) - gives duplicate groups

    ExpandableListAdapter a = new IRCursorAdapter (this,
            c, android.R.layout.simple_expandable_list_item_1, new String[] { "bond" },
            new int[] { android.R.id.text1 }, R.layout.row_doubleend,
            new String[] { "name", "ir" }, new int[] { R.id.double_item1, R.id.double_item2 });

    this.setListAdapter(a);


}

protected class IRCursorAdapter extends SimpleCursorTreeAdapter {

    public IRCursorAdapter(Context context, Cursor cursor, int groupLayout,
            String[] groupFrom, int[] groupTo, int childLayout,
            String[] childFrom, int[] childTo) {
        super(context, cursor, groupLayout, groupFrom, groupTo, childLayout, childFrom,
                childTo);
    }开发者_运维百科

    @Override
    protected Cursor getChildrenCursor(Cursor groupCursor) {

        Cursor c = db.query("spectro", new String[] {"_id", "name", "bond", "ir"},
                "bond=?", new String[] { groupCursor.getString(0) }, null, null, null);

        startManagingCursor(c);
        return c;
    }


};

here's what my table looks like- the bond is what I want to be unique:

_id | name | bond | ir

1 | name 1 | bond 1 | ir 1

2 | name 2 | bond 1 | ir 2

3 | name 3 | bond 2 | ir 3

4 | name 4 | bond 3 | ir 4

sorry if this isn't particularly clear, but thanks for your help!

I think you are mis-using the distinct operator. IT will return only Bond1, Bond2, Bond3 and that's it. You need to create a compound join statement to produce the correct cursor. What you have there will always throw an error because you haven't specified children correctly. Secondly, I would use the query (instead of raw query) and specify parameters there.

E.g. db.query(true, "spectro", new String[]{"bond"}, null,null,...)

You will also need to make sure you have your Group From -->To parameters and Children From --> to parameters correct!