Android: SQLite GROUP BY

Question

In my application, I have a database table containing chat messages,like below.

|---------------------------------------------|
|message      | from  | to   |time
|-------------|-------|------|----------------|
|Hello        |user1  |user2 |2015-2-26 1:15PM|
|-------------|-------|------|----------------|
|Watsup       |user2  |user1 |2015-2-26 1:25PM|
|-------------|-------|------|----------------|
|Hows u?      |user3  |user1 |2015-2-26 2:15PM|
|-------------|-------|------|----------------|
|Im fine      |user1  |user3 |2015-2-26 2:35PM|
----------------------------------------------|

In my messages page I want list messages from all users. In this condition assume, "user1" as log-inned user,

Currently I am using query,

SELECT * FROM table GROUP BY from

and I am getting output as,

    |--------------------------------|
    |user2                           |
    |Hello                           |
    |--------------------------------|
    |user2                           |
    |Watsup                          |
    |--------------------------------|
    |user3                           |
    |Hows u?                         |
    |--------------------------------|
    |user3                           |
    |Im fine                         |
    |--------------------------------|

What I want is distinct rows (like all chat apps),

|--------------------------------|
|user2                           |
|Watsup                          |
|--------------------------------|
|user3                           |
|Im fine                         |
|--------------------------------|

So, how can I write a sqlite query to fetch rows like this?

I am baffled at how you suggested those two messages from the output. Can you elaborate on the logic you want to implement? — Gordon Linoff, Feb 26 '15 at 11:49
@Gordon Linoff: I want to get the latest message.And there is a column for date and time in my table. Sorry I missed it :) — Basim Sherif, Feb 26 '15 at 11:51
Can you post your table structure? With column time and date — programmer23, Feb 26 '15 at 11:52
Have you tried an order by as here: http://stackoverflow.com/questions/9902394/how-to-get-last-record-from-sqlite? — flp, Feb 26 '15 at 12:01

Mahesh · Answer 1 · 2015-02-26T12:52:02.737

2

You can try as below with the Group By and Having clause.

     SELECT message,from
     FROM 
        table 
    GROUP BY from
    HAVING Max(time) = Time

edited Feb 26 '15 at 12:52

answered Feb 26 '15 at 11:59

Mahesh

8,694
2
32
53

Thanks. I tried, but getting same result as before. – Basim Sherif Feb 26 '15 at 12:16

score 1 · Answer 2 · answered Oct 14 '15 at 06:46

You can try something like this:

SELECT * FROM table GROUP BY MIN(from, to), MAX(from, to) ORDER BY time DESC

This will not treat GROUP BY of (userA, userB) and (userB, userA) as different and combine the two, so you will get the output as you want.

You can also JOIN this with your Contacts table so that you can get all the data you need to show on the "Recent Chats" screen in one go. Try something like this:

SELECT * FROM messages m, Contacts c WHERE (m.from = c.userid OR m.to = c.userid) GROUP BY MIN(m.from, m.to), MAX(m.from, m.to) ORDER BY m.time DESC

score 0 · Answer 3 · answered Feb 26 '15 at 13:14

0

Use DISTINCT AND ORDERBY :

SELECT DISTINCT from,message FROM table ORDER BY time DESC;

answered Feb 26 '15 at 13:14

user543

3,623
2
16
14

Android: SQLite GROUP BY

3 Answers3