SQL MAX(m.time) does not select the highest value

The piece of code:

SELECT m.message, 
          MAX(m.time) AS time, 
          m.sender, 
          m.receiver, 
          m.contact, 
          u.ufir开发者_开发知识库stname
FROM( (SELECT message, 
                  receiver_uid AS receiver, 
                  sender_uid AS sender, 
                  time, 
                  receiver_uid AS contact 
             FROM messages 
            WHERE sender_uid = '$me' ) 
           UNION 
           (SELECT message, 
                   receiver_uid AS receiver, 
                   sender_uid AS sender, 
                   time, 
                   sender_uid AS contact 
              FROM messages 
             WHERE receiver_uid = '$me' ) ) AS m, 
         users AS u
   WHERE m.contact = u.uid
GROUP BY m.contact
ORDER BY time DESC;

My problem is that this piece SHOULD select the most recent message of a conversation. This is the format of the database:

Sender_uid | receiver_uid | message | time («What format should this be?)

The first part retrieves all the messages in which '$me' (=userID of logged in user) takes part. So all the messages whether '$me' is sending or receiving. The second part should select the most recent time (highest value) for each 'conversation' (=contact ID, whether contact is receiver or sender)

It does not get the most recent message. It does give you one message from the conversation.

Important hint(?): What I think the script is doing is retrieving the most recent message where '$me' is the sender.

I want it to collect the last message whether $me is receiver or sender.

Please stop abusing MySQL with mixing aggregates and non-aggregates. While MySQL allows it, it is unpredictable which records each column comes from, and they don't even have to be from the same record (in sync)

SELECT m2.message, 
          m2.time AS time, 
          m2.sender, 
          m2.receiver, 
          m.contact, 
          u.ufirstname
FROM (
    SELECT
        case when sender_uid = '$me' then receiver_uid else sender_uid end Contact,
        max(time) maxtime
    FROM messages 
    WHERE sender_uid = '$me' or receiver_uid = '$me'
    group by case when sender_uid = '$me' then receiver_uid else sender_uid end
) AS m
INNER JOIN messages m2 on m.maxtime = m2.time
    and ((m2.sender_uid = '$me' and m2.receiver_uid = m.Contact)
        or
        (m2.receiver_uid = '$me' and m2.sender_uid = m.Contact))
INNER JOIN users AS u on m.contact = u.uid
ORDER BY time DESC;

@updated query - was more complicated that it looked.

• The "Contact" is the other party of the conversation.
• The inner OR is used to pass through the table only once.
• The inner CASE statement is used to get the ID of the other party. If '$me' is matched to the sender, the receiver becomes the "contact" otherwise the sender is the "contact"
• The join with m2 uses 3 attributes - me, contact (either orientation) and maxtime This join uses the keys from the inner query (maxTime) to retrieve the full record from the table
• There is a final join with users to get the contact's firstname