timv timv - 12 days ago 6
Android Question

SELECT DISTINCT Not working Android SQLite

(NOTE question updated with what I have worked out so far)
I am writing an app that has locations recorded by the following:

id(primary), state, region, area, latitude, longitude


I can get all the data on the screen but I just want to list the states and only the states not any other data. Also just one entry per unique entry. ie 1 NSW for NSW not all 99 of them. A user will then select the desired state they live in and it will then show a new table of data just for that state. (by states I mean NSW, VIC, QLD etc)

The class' Dataprovider and recyclerview classes work fine too so I will keep that code out of it unless someone wants to see it.

Here is the Database class in question.

public class MyDatabase extends SQLiteAssetHelper {

private static final String DATABASE_NAME = "ReefData.db";
private static final int DATABASE_VERSION = 1;

public MyDatabase(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
setForcedUpgrade();

}

public Cursor getData(){
SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
String [] sqlSelect = {"0 _id", "state", "area", "region", "latitude", "longitude"};
String sqlTables = "Reefs";
qb.setTables(sqlTables);
Cursor c = qb.query(db, sqlSelect, null, null, null, null, null);
c.moveToFirst();
return c;
}}


So my first attempt was as follows:

String [] sqlSelect = {"0 _id", "state=DISTINCT", "area", "region", "latitude", "longitude"};


but that just gives me an error.

SO I changed it back to the orignal and modified the query as follows:

String [] sqlSelect = {"0 _id", "state", "area", "region", "latitude", "longitude"};
Cursor c = qb.query(db, sqlSelect, "state= 'TAS'", null, null, null, null);


And this works now just for TAS data. I can even modify it with

Cursor c = qb.query(db, sqlSelect, "state= 'TAS' and region='Flinders Island'", null, null, null, null);


and it works great.

BUT, how can I get a list of just the states and only 1 of each. I would of thought to do the following but it does not work:

Cursor c = qb.query(db, sqlSelect, "state = DISTINCT", null, null, null, null);


Any ideas?

UPDATE: SO I have tried the following in the attached image so I am getting closer to the solution but not quite there. Still need help to solve this.

enter image description here

Answer

SOLVED!

So SQLiteQueryBuilder methods that return a Cursor don't allow the Boolean parameter directly.

However after reading the class notes I noticed the method setDistinct and applied it to the SQLiteQueryBuilder.

Now it works and gives me just one unique entry for each state, just what I was trying to do. I did need to remove the other columns and just focus on the state, but thats another issue/solution not related to this question. Suffice to say I had to focus on just the column state and apply the setDistinct to that column only to get the desired result.

Heres the completed code:

public Cursor getData(){
    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
    String [] sqlSelect = {"0 _id", "state"};
    String sqlTables = "Reefs";
    qb.setTables(sqlTables);
    qb.setDistinct(true);
    Cursor c = qb.query(db, sqlSelect, null, null, null, null, null);
    c.moveToFirst();
    return c;
}
Comments