How to group by rows but with the most recent date?

Question

In my MySQL database I have a table like this used for storing conversation messages from any people

id               int(11)           id of the message    
from member_id   int(11)           id of the person the message was sent from
to member_id     int(11)           id of the person the message was sent to 
date sent        datetime          date of when it was sent 
active           tinyint(1)        if the message is deleted    
text             longtext          the text of the message
from_read        tinyint(1)        boolean to know if the person who sent it read it 
to_read          tinyint(1)        boolean to know if the person who it got sent to read it

So for example, it could have like:

from_member_id to_member_id date sent
1              2            june 12
1              3            june 13
2              3            june 14
3              1            june 9

So we have a conversation between person 1 and 2, 1 and 3, 2 and 3.

I am trying to get a select statement which will give me the most recent message that the current user is involved with from every conversation that user is in. So if 1 is logged in then I would expect to get 2 rows. The first row in the result set would be the second row above (july 13) because its the most recent, then then the second row in the result set would be the first row above (june 12), which are the most recent from 1's two conversations. The result set also needs to be sorted by date sent, so newer conversations are listed on top.

What I am trying to do is like the texting in android phones, where you see the list of conversations, and the most recent message in each listing.

This is my sql query

SELECT *
FROM (
    SELECT   *
    FROM     message
    WHERE `from member_id`=1 OR `to member_id`=1
    ORDER BY IF(`from member_id`=1, `to member_id`, `from member_id`)
) as t
GROUP BY IF(`from member_id`=1, `to member_id`, `from member_id`)

I just hardcoded 1 for now to be the current user. What I am doing is, sorting them by the id of the other person which I can check using the if statement, then grouping that result so I try to get the recent one from each conversation.

The problem is that when grouping, each group can have more than 1 rows, and it just seems to pick some random row. How can I get it to pick the row that has the most recent date sent value?

peterm · Accepted Answer · 2013-08-22T04:46:21.453

Are you looking for something like this?

SELECT m.*
  FROM message m JOIN
(
  SELECT from_member_id, to_member_id, MAX(date_sent) date_sent
    FROM message
   WHERE from_member_id = 1
   GROUP BY from_member_id, to_member_id
) q
    ON m.from_member_id = q.from_member_id
   AND m.to_member_id = q.to_member_id
   AND m.date_sent = q.date_sent
 ORDER BY date_sent DESC

Sample output:

| FROM_MEMBER_ID | TO_MEMBER_ID |  DATE_SENT |
----------------------------------------------
|              1 |            3 | 2013-06-13 |
|              1 |            2 | 2013-06-12 |

Here is SQLFiddle demo

UPDATE

SELECT m.*
  FROM message m JOIN
(
  SELECT LEAST(from_member_id, to_member_id) least_id, 
         GREATEST(from_member_id, to_member_id) greatest_id, 
         MAX(date_sent) date_sent
    FROM message
   WHERE from_member_id = 1 
      OR to_member_id   = 1
   GROUP BY    LEAST(from_member_id, to_member_id),
            GREATEST(from_member_id, to_member_id)
) q
    ON LEAST(m.from_member_id, m.to_member_id) = q.least_id
   AND GREATEST(m.from_member_id, m.to_member_id) = q.greatest_id
   AND m.date_sent = q.date_sent
 ORDER BY date_sent DESC

Sample output:

| FROM_MEMBER_ID | TO_MEMBER_ID |  DATE_SENT |
----------------------------------------------
|              3 |            1 | 2013-06-14 |
|              1 |            2 | 2013-06-12 |

Here is SQLFiddle demo

This is working when the most recent message is from the current user. But it doesn't consider messages that are sent from the other user to the current user. — omega, Aug 22 '13 at 04:08
I ended up getting it to work using a technique called groupwise maximum. http://stackoverflow.com/questions/15211479/groupwise-maximum — omega, Aug 29 '13 at 13:01

score 0 · Answer 2 · edited Aug 22 '13 at 06:23

0

SELECT
  *
FROM message m INNER JOIN
  (
    SELECT
      from_menber_id,
      MAX(date_sent) AS sentdate
    FROM message s
    GROUP BY from_menber_id
  ) AS a
    ON m.date_sent = a.sentdate AND a.from_menber_id = m.from_menber_id

edited Aug 22 '13 at 06:23

Alexander Yancharuk

13,817
5
55
55

answered Aug 22 '13 at 03:52

Nisha

1,379
16
28

Sorry it was the name of the table. I have edited it. Please see if this is what you needed. – Nisha Aug 22 '13 at 05:25

How to group by rows but with the most recent date?

2 Answers2