CODEWITHSUNDEEP
mysqlsqldatetimemessage
Gmeister4
Gmeister4

Reputation: 755

mySQL select the last message from each conversation

I have a user's table that stores all their sent/ received messages:

id | data_type | user_name | body | datetime

And what I want to achieve is:

1) To select the last row from each conversation, i.e the last inserted row of each unique user_name in the table that has a data_type of 1 (received message) or 2 (sent message).

2) Only get the rows that are older than a specific datetime, i.e WHERE datetime < from_datetime. (datetime is stored as UTC).

Does anyone know how I can achieve this? This is beyond my level of expertise.

This is what I have so far: 

SELECT MAX(id) as id, body, user_name 
FROM user_harry
WHERE data_type = 1 OR data_type = 2
GROUP BY user_name

It returns the correct last id's of the rows, but the body's are not the ones from that row. Thanks for the help.

Upvotes: 0

Views: 1080

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

If your existing query produces the correct ID values that you want then this should get you that and the body values associated with those rows:

select y.*
  from (select max(id) as max_id, user_name
          from user_harry
         where data_type in (1, 2)
         group by user_name) x
  join user_harry y
    on x.max_id = y.id
   and x.user_name = y.user_name

Upvotes: 2

Ahmet Vehbi Olga&#231;
Ahmet Vehbi Olga&#231;

Reputation: 2935

I hope it works

SELECT user_harry.*
FROM user_harry INNER JOIN (
    SELECT MAX(id) as max_id, user_name 
    FROM user_harry
    WHERE data_type IN(1,2)
    GROUP BY user_name
) temptable ON user_harry.id = temptable.max_id AND user_harry.user_name = temptable.user_name

Ahmet Vehbi Olgaç

Upvotes: 2

Related Questions