10

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 is producing the correct results however they are not in the correct order.

The grouping works well but it record displayed in the group is the first recorded entered into the DB but I would like the latest record to be displayed in each group.

Is there a way to have the latest record displayed 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 that is the most recent record/message.

Cheers

Improve this question
2
  • What is the query supposed to show? Commented Dec 19, 2011 at 1:50
  • If you need the two columns only (e.g. ID and its latest timestamp), this might work: stackoverflow.com/a/4448536/722036. It's faster than using sub-queries on a huge table with millions of rows. Commented Jun 26, 2016 at 11:38

4 Answers 4

Reset to default
10

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This seems to be working. A slight modification needed (add tbl_messages before the GROUP BY) but looks good. Thanks!
Thanks save my day, in school I learned more about subquerys... I did not remember this greats querys ... my teacher ... old memories
8

Do a GROUP BY after the ORDER BY by wrapping your query with the GROUP BY like this:

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

1 Comment

Even if I put the ASC in the query "t" its taking DESC.
2

If your messages table has a primary key that is auto-increment, and all messages are by nature highest number is the most recent date... However, since I don't KNOW that, I am going based on the 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
Improve this answer

2 Comments

how would I add ORDER BY in MaxPerUser and GROUP BY at the end of the query.
@MohammedAbrarAhmed, no order by applicable to the inner MaxPerUser query. However, if you wanted your outer results based on the most recent message date, you could ORDER BY MaxPerUser.LatestMsgDate -- if that is what you are considering to have the most recent messages float to the top.
-1

Do 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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.