0

I have an ArrayList variable named counties and I am extracting data from my database and adding it to this ArrayList with the following code:

//Get all data from database
        Cursor cursor = database.rawQuery("SELECT * FROM " + tableName, null);



//Add Counties to ArrayList
        cursor.moveToFirst();
        while(!cursor.isAfterLast()){
            counties.add(cursor.getString(cursor.getColumnIndex("COUNTY")));
            cursor.moveToNext();
        }

I am then displaying this data in a ListView which will allow the user to select a County and be taken to another activity.

The data is such that there are multiple records with the same county, thus it looks like:

COUNTIES
______________
West Yorkshire
West Yorkshire
Lancashire
Warwickshire
Cornwall
Lancashire
West Yorkshire

I want only one instance of each county to be shown and have struggled thus far to find a solution.

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
Imminence
  • 49
  • 1
  • 3
  • 7
  • get all the data, and then take that data and put it into a hashmap. as long as the key of the hashmap is the same, it wont add dupes. you can then iterate throuhg the hashmap to get the values. – letsCode Nov 15 '17 at 16:18
  • Thanks a lot for the answer Droi, just to clarify, would the data be stored directly into the HashMap from my cursor code above, or would I move the data from my ArrayList into my HashMap and then into my ListView? – Imminence Nov 15 '17 at 16:22
  • I'm curious why you're bringing back all the data and then only saving what you want. Normally I'd do something like SELECT DISTINCT county FROM tablename... – KathyA. Nov 15 '17 at 16:22
  • Kathy, I use the other rows from the table at a later point in my code, it's a fair point though! – Imminence Nov 15 '17 at 16:22
  • @Imminence I havent worked with SQLIte.... but however you query the DB, get everything and put it into the hashmap. You can use the ArrayList as well, whatever you want. If you would iterating through the arraylist and adding to hashmap. if (countryname.equals("abc")) hashmap.put("abc", "cornwall") else if() ... you can also do a switch statement in another method to save on space. – letsCode Nov 15 '17 at 16:24
  • You'll probably want to `... GROUP BY County` – Phantômaxx Nov 15 '17 at 16:29
  • 1
    @BernoulliGate Just looked into GROUP BY, this seems to be the best method, thanks. – Imminence Nov 15 '17 at 16:31
  • The irony of this getting marked duplicate is not lost on me. – Imminence Nov 15 '17 at 16:36

1 Answers1

2

Use this

Cursor cursor = database.rawQuery("SELECT DISTINCT "+city_column_name+" FROM "+tableName, null);

Instead of :

Cursor cursor = database.rawQuery("SELECT * FROM "+tableName, null);

Sohrab Alam
  • 111
  • 4