MySQL selects the GROUP BY command

Question

I have a mysql statement

SELECT * FROM tbl_messages WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' GROUP BY from_user_id ORDER BY date_sent DESC 

and it produces the correct results, but they are not in the correct order.

Grouping works well, but the record displayed in the group is the first record entered in the database, but I would like the last record to be displayed in each group.

Is there a way to display the last record for each group?

 2011-12-19 12:16:25 This is the first message 2011-12-19 12:18:20 This is the second message 2011-12-19 12:43:04 This is the third message 

The group shows “This is the first message,” where I would like “This is the third message,” as this is the most recent post / message.

Greetings

Answer 1

This may work (but not guaranteed):

 SELECT * FROM ( SELECT * FROM tbl_messages WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' ORDER BY date_sent DESC ) tmp GROUP BY from_user_id ORDER BY date_sent DESC 

---

This should work:

 SELECT t.* FROM tbl_messages AS t JOIN ( SELECT from_user_id , MAX(date_sent) AS max_date_sent FROM tbl_messages WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' GROUP BY from_user_id ) AS tg ON (tg.from_user_id, tg.max_date_sent) = (t.from_user_id, t.date_sent) ORDER BY t.date_sent DESC 

Answer 2

Make GROUP BY after ORDER BY, wrapping your query with GROUP BY as follows:

 SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from 

Answer 3

If your message table contains an auto-incremented primary key, and all messages are by nature the highest number, this is the latest date ... However, since I don't know, I'm going to base MAX (date_sent) instead of max (SomeIDKey), but the principle is the same.

 select tm2.* from ( select tm1.from_user_id, max( tm1.date_sent ) LatestMsgDate from tbl_messages tm1 group by tm1.from_user_id ) MaxPerUser left join tbl_messages tm2 on MaxPerUser.From_User_ID = tm2.From_User_ID AND MaxPerUser.LatestMsgDat = tm2.Date_Sent order by date_sent DESC 

Answer 4

You mean something like this:

 SELECT * FROM tbl_messages WHERE to_user_id = '$ user_id' OR from_user_id = '$ user_id' GROUP BY from_user_id ORDER BY from_user_id, date_sent DESC