1. Tablet future looks bright! Read more about: Alcatel Xess | Samsung Galaxy View
    New Forums: Alcatel Xess | Samsung Galaxy View
    Dismiss Notice

SQLite - How to specify which columns should be distinct?


Last Updated:

  1. scrapperstoo

    scrapperstoo Member

    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 (Text):
    1. public Cursor  fetchUniqueMembers(String colKey) throws SQLException {
    2.         String[] cols = new String[] {KEY_ROWID, colKey};
    3.         Cursor mCursor =
    4.         mDb.query(true, DATABASE_TABLE, cols, null, null, null, null, colKey + " ASC", null);
    5.             if (mCursor != null) {
    6.             mCursor.moveToFirst();
    7.         }
    8.         return mCursor;
    9.     }
    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 :)

    Advertisement
  2. scrapperstoo

    scrapperstoo Member

    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 (Text):
    1. public ArrayList<String> fetchUniqueMembers(String colKey) throws SQLException {
    2.         String[] cols = new String[] {colKey};
    3.         Cursor mCursor =
    4.         mDb.query(false, DATABASE_TABLE, cols, null, null, null, null, colKey + " ASC", null);
    5.             if (mCursor != null) {
    6.             mCursor.moveToFirst();
    7.         }
    8.             int colIndex =  mCursor.getColumnIndex(colKey);
    9.             ArrayList<String> mArrayList = new ArrayList<String>();
    10.             for(mCursor.moveToFirst();mCursor.moveToNext();mCursor.isAfterLast()){
    11.                 mArrayList.add(mCursor.getString(colIndex));
    12.             }
    13.         return mArrayList;
    14.     }
  3. scrapperstoo

    scrapperstoo Member

    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 (Text):
    1. for(mCursor.moveToFirst();!mCursor.isAfterLast();mCursor.moveToNext()){
    2.                 mArrayList.add(mCursor.getString(colIndex));
    3.             }
  4. That Don Guy

    That Don Guy Member

    Would something like this work:
    Code (Text):
    1.  
    2.         String[] cols = new String[] {"MIN(" + KEY_ROWID + ") AS rowid", colKey};
    3.         Cursor mCursor =
    4.         mDb.query(true, DATABASE_TABLE, cols, null, null, colKey, null, colKey + " ASC", null);
    5.  
    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 likes this.
  5. scrapperstoo

    scrapperstoo Member

    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!
  6. scrapperstoo

    scrapperstoo Member

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

    alostpacket Over Macho Grande? VIP Member

    you may find this a bit cleaner too:

    Code (Text):
    1.  
    2. if ( cursor.moveToFirst() )
    3. {
    4.     do
    5.     {
    6.          //stuff
    7.  
    8.     } while ( cursor.moveToNext() )
    9. }
    10.  
    11.  
    I'm usually a for-loop kinda guy myself but do-while really fits the iterator type stuff well.
    scrapperstoo likes this.
  8. scrapperstoo

    scrapperstoo Member

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

Share This Page