Go Back   Android Forums > Android Development > Application Development
Application Development Dev lounge for our application developers.

Get excited for the Samsung Galaxy S5! Find everything you need and discuss it in our Galaxy S5 Forum!

test: Reply
 
LinkBack Thread Tools
Old March 2nd, 2011, 06:41 AM   #1 (permalink)
New Member
Thread Author (OP)
 
Join Date: Dec 2010
Posts: 9
 
Device(s):
Carrier: Not Provided

Thanks: 3
Thanked 0 Times in 0 Posts
Default SQLite - How to specify which columns should be distinct?

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.

Code:
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 is offline  
Reply With Quote
sponsored links
Old March 2nd, 2011, 11:02 AM   #2 (permalink)
New Member
Thread Author (OP)
 
Join Date: Dec 2010
Posts: 9
 
Device(s):
Carrier: Not Provided

Thanks: 3
Thanked 0 Times in 0 Posts
Default

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?

Code:
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 is offline  
Reply With Quote
Old March 2nd, 2011, 11:43 AM   #3 (permalink)
New Member
Thread Author (OP)
 
Join Date: Dec 2010
Posts: 9
 
Device(s):
Carrier: Not Provided

Thanks: 3
Thanked 0 Times in 0 Posts
Default

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 -

Code:
for(mCursor.moveToFirst();!mCursor.isAfterLast();mCursor.moveToNext()){
    			mArrayList.add(mCursor.getString(colIndex));
    		}
scrapperstoo is offline  
Reply With Quote
Old March 2nd, 2011, 11:56 AM   #4 (permalink)
New Member
 
Join Date: Feb 2011
Posts: 7
 
Device(s):
Carrier: Not Provided

Thanks: 0
Thanked 1 Time in 1 Post
Default

Would something like this work:
Code:
        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
That Don Guy is offline  
Reply With Quote
The Following User Says Thank You to That Don Guy For This Useful Post:
scrapperstoo (March 3rd, 2011)
Old March 4th, 2011, 12:43 AM   #5 (permalink)
New Member
Thread Author (OP)
 
Join Date: Dec 2010
Posts: 9
 
Device(s):
Carrier: Not Provided

Thanks: 3
Thanked 0 Times in 0 Posts
Default

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 is offline  
Reply With Quote
Old March 4th, 2011, 04:52 AM   #6 (permalink)
New Member
Thread Author (OP)
 
Join Date: Dec 2010
Posts: 9
 
Device(s):
Carrier: Not Provided

Thanks: 3
Thanked 0 Times in 0 Posts
Default

Just to let you know - it works a treat. Cheers!
scrapperstoo is offline  
Reply With Quote
Old March 4th, 2011, 08:58 PM   #7 (permalink)
Over Macho Grande?
 
alostpacket's Avatar
 
Join Date: Nov 2009
Location: NY
Posts: 7,873
 
Device(s): GlassXE, MotoX, N5, N4, N7'12, GNex, N1, SGT10.1, Revue, Xoom, Eris, OG Droid
Carrier: TMO

Thanks: 4,582
Thanked 3,563 Times in 1,522 Posts
Default

you may find this a bit cleaner too:

Code:
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.
alostpacket is offline  
Reply With Quote
The Following User Says Thank You to alostpacket For This Useful Post:
scrapperstoo (March 5th, 2011)
Old March 5th, 2011, 01:11 AM   #8 (permalink)
New Member
Thread Author (OP)
 
Join Date: Dec 2010
Posts: 9
 
Device(s):
Carrier: Not Provided

Thanks: 3
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by alostpacket View Post
you may find this a bit cleaner too:

Code:
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
scrapperstoo is offline  
Reply With Quote
Reply
Tags
distinct, list view, sqlitedatabase


Go Back   Android Forums > Android Development > Application Development
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT -5. The time now is 12:04 AM.
Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.