CODEWITHSUNDEEP
androidsqlite
Robin Westerlundh
Robin Westerlundh

Reputation: 874

Android SQLite distinct select?

I have created a Sqlite database in my android app, it looks like this:

create table msgs ( id integer primary key autoincrement, msg text not null, session text not null, sender text not null);

I can get all the entry's like this, but I don't fly understand what is happening.

   String[] allColumns = {"msg","session","sender"};
   Cursor cursor = database.query(msgs, allColumns, id = insertId, null,  null, null, null);

What i could like to do, is to only get the latest entry with a distinct session how can i do this in android?

Edit: If this was mysql i whould do "SELECT MAX(id) AS id2,msg, session FROM msgs GROUP BY session" But cant seam to get it to work in SQLite :/

Upvotes: 2

Views: 369

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11161

Always check documentation and be careful with types!

Your using following method:

query(
    msgs, //String table: OK
    allColumns, //String[] columns: OK
    id = insertId, //String selection: NOT OK
    // ^--- this is a boolean which will render SQL something like
    // "SELECT ... WHERE TRUE ..." or "SELECT ... WHERE FALSE ..."
    // causing all rows or none to be displayed
    null, //String[] selectionArgs: OK
    null, //String groupBy: OK
    null, //String having: OK
    null); //String orderBy: OK

Correction:

query(
    msgs, //table
    allColumns, //columns
    "id = ?", //selection
    new String[] {String.valueOf(insertId)}, //selectionArgs
    null, //groupBy
    null, //having
    null); //orderBy

Upvotes: 0

CL.
CL.

Reputation: 180070

To execute a complete SQL query, you could use rawQuery:

cursor = database.rawQuery("SELECT MAX(id) AS id2,msg, session FROM msgs GROUP BY session", null);

With query, you would have to set the parameters like this:

cursor = database.query("msgs",
                        new String[] { "MAX(id) AS id2", "msg", "session" },
                        null, null,
                        "session",
                        null, null);

Please note that using an unaggregated colum (msg) in an aggregated query does not work before SQLite version 3.7.11 (Android API version 16, Jelly Bean).

Upvotes: 1

Related Questions