CODEWITHSUNDEEP
androidsqlite
user3307005
user3307005

Reputation: 216

sqlite rawquery not working correctly

I have two table their names are profile and messages. I try to get profile data and last message which related by profileId from sql.

profiles

messages

data examples

table contents

enter image description here

my query

SQLiteDatabase db = this.getReadableDatabase();


     String selectQuery = "select " +
     "profiles.profileId," +
     "profiles.profileName," +
     "profiles.profileImgPath," +
     "profiles.profileStatus," +
     "messages.messageContent," +
     "messages.messageTime " +
     "from profiles left join messages on profiles.profileId=messages.profileId " +
     "group by profiles.profileId " +
     "order by messages.messageId DESC;";
          Cursor c = db.rawQuery(selectQuery, null);
    Log.i("query",selectQuery);
    if (c.moveToFirst()) {
        do {

            Log.i("gokberksql", c.getString(1) + "x" + c.getString(4));
            } while (c.moveToNext());
    }

results

gokberksql﹕ sinanxKanka naber

But need to return 

gokberksql﹕ sinanxIyi senden naber

In addition this query working well in my computer editors but android results not correctly.

Upvotes: 0

Views: 148

Answers (2)

Karakuri
Karakuri

Reputation: 38595

You should select the max messageTime in your query. This will give you the values of columns in the same row as the one that contains the max.

String selectQuery = "select " +
    "profiles.profileId," +
    "profiles.profileName," +
    "profiles.profileImgPath," +
    "profiles.profileStatus," +
    "messages.messageContent," +
    "max(messages.messageTime) as messageTime " + // <- here
    "from profiles " +
    "left join messages on profiles.profileId = messages.profileId " +
    "group by profiles.profileId" +
    "order by profiles.profileId, messages.messageId DESC;";

Note that this behavior was introduced in SQLite version 3.7.11, which became standard in API 16 (JellyBean). If you need to support API 15 or older, you may have to use a different query.

Upvotes: 1

Bajji
Bajji

Reputation: 2393

Why do you have a group by profiles.profileId, that is not needed in your case since you are not doing any aggregations over the profileId group. Try with 

String selectQuery = "select " +
   "profiles.profileId," +
   "profiles.profileName," +
   "profiles.profileImgPath," +
   "profiles.profileStatus," +
   "messages.messageContent," +
   "messages.messageTime " +
 "from profiles left join messages on   profiles.profileId=messages.profileId " +
   "order by profiles.profileId, messages.messageId DESC;";

Upvotes: 0

Related Questions