Apps SQLite - How to specify which columns should be distinct?

[scrapperstoo]

Beginner Android dev here - so please forgive me if this is a stupid question!

I have a table from which I am trying to display a single column only, with no duplication.

public Cursor  fetchUniqueMembers(String colKey) throws SQLException {
    	String[] cols = new String[] {KEY_ROWID, colKey};
    	Cursor mCursor = 
    	mDb.query(true, DATABASE_TABLE, cols, null, null, null, null, colKey + " ASC", null);
    		if (mCursor != null) {
    		mCursor.moveToFirst();
    	}
    	return mCursor;
    }

The results then get displayed in a list via a SimpleCursorAdapter.

I need to keep the rowid column in the results to allow the list to display, but if I do, then the "distinct" part of the query doesn't have any effect - because each rowid is obviously unique.


Any chance someone can point me in the right direction?

Cheers

 

[scrapperstoo]

I've managed to almost sort out a workaround by dumping the Cursor contents into an Arraylist.

It's still not working quite right though, as it seems to ignore the first item in the list each time. Can't work out what I'm doing wrong here?

public ArrayList<String> fetchUniqueMembers(String colKey) throws SQLException {
    	String[] cols = new String[] {colKey};
    	Cursor mCursor = 
    	mDb.query(false, DATABASE_TABLE, cols, null, null, null, null, colKey + " ASC", null);
    		if (mCursor != null) {
    		mCursor.moveToFirst();
    	}
    		int colIndex =  mCursor.getColumnIndex(colKey);
    		ArrayList<String> mArrayList = new ArrayList<String>();
    		for(mCursor.moveToFirst();mCursor.moveToNext();mCursor.isAfterLast()){
    			mArrayList.add(mCursor.getString(colIndex));
    		}
    	return mArrayList;
    }

 

[scrapperstoo]

Finally got there - looks like I'd copied a mistake in the for loop without noticing..... it was just about the only part I thought wasn't likely to be wrong!

Now working nicely with it changed to -

for(mCursor.moveToFirst();!mCursor.isAfterLast();mCursor.moveToNext()){
    			mArrayList.add(mCursor.getString(colIndex));
    		}

 

[That Don Guy]

Would something like this work:

        String[] cols = new String[] {"MIN(" + KEY_ROWID + ") AS rowid", colKey};
        Cursor mCursor = 
        mDb.query(true, DATABASE_TABLE, cols, null, null, colKey, null, colKey + " ASC", null);

Since you want to get just one row for each distinct value in column <colKey>, you should GROUP BY that column; presumably, which particular row you get is not important, so get the minimum <KEY_ROWID> column value for each particular <colKey> value.

However, I am not 100% sure that this syntax is correct - personally, as a professional SQL coder (and a control freak), I prefer using rawQuery.

-- Don

 

[scrapperstoo]

Thanks - that very much looks like the way to go.

I've not touched any SQL (or java) since I was at uni over 10 years ago and had completely forgotten about GROUP BY - but now you've pointed it out it seems painfully obvious.

Can't see why it wouldn't work, unless the CursorAdapter to list requires the rowid column entries to be consecutively numbered as well as unique. Don't think that's the case, but it'd be easy enough to work around if need be.

I'll give it a go once I've worked out a few more pressing problems - thanks again!

 

[scrapperstoo]

Just to let you know - it works a treat. Cheers!

 

[alostpacket]

you may find this a bit cleaner too:

if ( cursor.moveToFirst() )
{
    do
    {
         //stuff

    } while ( cursor.moveToNext() )
}

I'm usually a for-loop kinda guy myself but do-while really fits the iterator type stuff well.

 

[scrapperstoo]

you may find this a bit cleaner too:

if ( cursor.moveToFirst() )
{
    do
    {
         //stuff

    } while ( cursor.moveToNext() )
}

I'm usually a for-loop kinda guy myself but do-while really fits the iterator type stuff well.

Ooh yeah - that does look like a more elegant way of doing it. Cheers