Select Distinct Value in Android SQLite

select distinct value in android sqlite

You can use this method:

public Cursor query (boolean distinct, String table, 
                     String[] columns, String selection, 
                     String[] selectionArgs, String groupBy, 
                     String having, String orderBy, String limit)

Here first argument specifies whether to use distinct or not.

Sqlite select distinct with Where

Ok, then the WHERE clause should look like this:

WHERE TYPE IN ('type2', 'type3')

And in the end, the whole line should look like this:

Cursor cursor = db.rawQuery("select DISTINCT " + KEY_NUMBER + " from " + TABLE + " WHERE TYPE IN ('type2', 'type3') ORDER BY KEY_NUMBER DESC", null);

display distinct records sqlite android

Solution
You want DISTINCT conditions but Android requires the _id column which is a problem because you cannot mix and match: SELECT _id, DISTINCT condition.... However you can use the GROUP BY clause instead:

return myDataBase.query(DB_TABLE, new String[] {"rowid as _id", KEY_CONDITIONS}, 
        builder.toString(), symptoms, KEY_CONDITIONS, null, null);

---

Explanations
This query:

return myDataBase.rawQuery("SELECT DISTINCT " + KEY_CONDITIONS + " FROM " 
    + DB_TABLE + " " + builder.toString() + symptoms.toString(), null);

Failed because you are passing String[] symptoms in the wrong parameter, try:

return myDataBase.rawQuery("SELECT DISTINCT " + KEY_CONDITIONS + " FROM " 
    + DB_TABLE + " " + builder.toString(), symptoms);

---

This query:

return myDataBase.query(true, DB_TABLE, new String[] {"rowid as _id", KEY_CONDITIONS}, builder.toString(), symptoms, null, null, null, null);

Failed because DISTINCT is looking at both the id and condition columns. It is the equivalent of: SELECT DISTINCT(_id, conditions) ... You, obviously, only want distinct conditions...

SELECT DISTINCT Not working Android SQLite

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;
}

how to use distinct in android room

I got the answer by making changes
I have change the query in CountryDao.java . rewrite the query to

@Query("SELECT DISTINCT name from country")
public List<String> getCountry();

Then call in the activity like this.

 List<String> co = database.countryDao().getCountry();
    for(int i=0; i<co.size();i++) {
        Log.d(TAG, "country:" + co.get(i));
    }

It's the problem is in my code get the list by object but i want it in strings. Thanks for all frienda.

How to make... SQLite select DISTINCT - query(true, ...)

First of all you should read SQLiteDatabase reference.

You could use a rawQuery(), here's an example of it :

rawQuery - db.rawQuery("select DISTINCT Y.CATE_CODE4 CATE_CODE, Y.CATE_NAME4 CATE_NAME,
             0 IS_PRESETG_MASTER X INNER JOIN CATEGORY_VIEW Y ON X.CATE_4 = Y.CATE_CODE4 from table where X.IS_SALE = 1 AND Y.IS_LOCKED4 = 0 AND Y.CATE_NAME2 not in ('XXXX')"",new String[]{"data"});

It's just an example, you have to adapt your code to this.

SQLite Android get distinct values

select * from gdata

 campain        num     name       file       score     player    
 -------------  ------  ---------  ---------  --------  --------- 
 Campain One    1       Level One  test1.xml  1221      john      
 Campain One    1       Level Two  test1.xml  122       john      
 Campain One    1       Level Two  test1.xml  122       dereck    
 Campain One    1       Level Two  test1.xml  122       jon       
 Campain Two    1       Level Two  test1.xml  122       jon       
 Campain Two    1       Level Two  test1.xml  122       jon       
 Campain Two    1       Level Two  test2.xml  122       jon       
 Campain Three  1       Level Two  test2.xml  1122      jon       
 Campain Three  1       Level Two  test2.xml  1122      jon       
 Campain Three  1       Level Two  test2.xml  1122      derekk  

---

select 'All' as campain ,'All'as player,sum(score) score from gdata
union
select campain,player,sum(score) as score from gdata group by campain,player

 campain        player     score    
 -------------  ---------  -------- 
 All            All        5319     
 Campain One    dereck     122      
 Campain One    john       1343     
 Campain One    jon        122      
 Campain Three  derekk     1122     
 Campain Three  jon        2244     
 Campain Two    jon        366      

---

select campain,sum(score) as score from gdata group by campain

 campain        score    
 -------------  -------- 
 Campain One    1587     
 Campain Three  3366     
 Campain Two    366   

---

select campain,player,max(score) from (
select campain,player,sum(score) as score from gdata 
group by campain,player order by score desc
) as b
group by campain  

 campain        player     score
 -------------  ---------  ------------- 
 Campain One    john       1343          
 Campain Three  jon        2244          
 Campain Two    jon        366   

---

Related Topics