CODEWITHSUNDEEP
androidsqlsqlite
user1256821
user1256821

Reputation: 1188

Get the latest message for each two clients using SQL

I am working on a some kind of chat app. I store every message in a SQLite table such as following

|   id   | fromId |  toId  | date | content |
---------------------------------------------
|      0 |   1423 |     90 |  ... |     ... |
|      1 |    324 |     90 |  ... |     ... |
|      2 |     90 |    324 |  ... |     ... |
|      3 |     43 |   1423 |  ... |     ... |
|      4 |    439 |    324 |  ... |     ... |
|      5 |     90 |    324 |  ... |     ... |
|      6 |    324 |     43 |  ... |     ... |

I am trying to write a SQL request that shows chat dialog previews. In other words, I need to get the most recent message for each pair of two subscribers.

I guess that this is a frequent question, but I could not find any working solution (or I was not able to use them correctly).

Upvotes: 0

Views: 60

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Fiddle: http://sqlfiddle.com/#!7/3f4c2/2/0

select t.*
  from tbl t
 where id = (select max(x.id)
               from tbl x
              where (x.fromid = t.fromid and x.toid = t.toid)
                 or (x.fromid = t.toid and x.toid = t.fromid))

In the fiddle I created these indexes:

create index fromto on tbl (fromid, toid);
create index tofrom on tbl (toid, fromid);

To show you that this solution will utilize them:

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

If you have indexes on chat(fromid, toid) and chat(toid, fromid), then the most efficient way may be:

select c.*
from chat c
where not exists (select 1
                  from chat c2
                  where c2.fromId = c.fromId and c2.toId = c.toId and c2.id > c.id
                 ) and
      not exists (select 1
                  from chat c2
                  where c2.fromId = c.toId and c2.toId = c.fromId and c2.id > c.id
                 );

SQLite should be able to use the indexes for each of the subqueries.

Upvotes: 1

Bulat
Bulat

Reputation: 6979

Try this:

SELECT * 
FROM chat c
WHERE NOT EXISTS 
 (SELECT * FROM chat 
 WHERE c.fromId IN (fromId, toId) AND c.toID IN (fromId, toID) AND c.id < id);

http://sqlfiddle.com/#!7/d57f3/2/0

Upvotes: 2

Related Questions