SQLite - How to specify which columns should be distinct?


Last Updated:

  1. scrapperstoo

    scrapperstoo Member This Topic's Starter

    Joined:
    Dec 6, 2010
    Messages:
    9
    Likes Received:
    0
    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 This Topic's Starter

    Joined:
    Dec 6, 2010
    Messages:
    9
    Likes Received:
    0
    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 This Topic's Starter

    Joined:
    Dec 6, 2010
    Messages:
    9
    Likes Received:
    0
    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

    Joined:
    Feb 24, 2011
    Messages:
    7
    Likes Received:
    1
    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 This Topic's Starter

    Joined:
    Dec 6, 2010
    Messages:
    9
    Likes Received:
    0
    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 This Topic's Starter

    Joined:
    Dec 6, 2010
    Messages:
    9
    Likes Received:
    0
    Just to let you know - it works a treat. Cheers!
     
  7. alostpacket

    alostpacket Over Macho Grande? VIP Member

    Joined:
    Nov 29, 2009
    Messages:
    7,978
    Likes Received:
    3,603
    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 This Topic's Starter

    Joined:
    Dec 6, 2010
    Messages:
    9
    Likes Received:
    0
    Ooh yeah - that does look like a more elegant way of doing it. Cheers :)
     

Share This Page

Loading...