MySQL GROUP BY/ORDER BY problem

Question

I have an MySQL query like this:

   SELECT
         MAX(messages.ID) as maxID,
         messages.from,
         messages.read,
               users.userName
      FROM
         messages
      LEFT OUTER JOIN
         users
              ON
              users.ID = messages.from
      WHERE
       messages.to = ?
      GROUP BY
       messages.from
      ORDER BY
       maxID DESC

While this works fine and the messages are group by the user who sent them in DESCENDING order, I now have a problem when I want to ORDER BY the messages.read row. My code is as follows:

   SELECT
         MAX(messages.ID) as maxID,
         messages.from,
         messages.read,
               users.userName
      FROM
         messages
      LEFT OUTER JOIN
         users
              ON
              users.ID = messages.from
      WHERE
       messages.to = ?
      GROUP BY
       messages.from
      ORDER BY
             messages.read ASC,
       maxID DESC

Now, messages.read returns the value of the row of the first message that the user send. But I need the value of the newest (highest ID) that the user send. I know that GROUP BY groups by the first in the table, so how can I get around doing this?

Thank you very much in advance, Fischer

Answer 1

I know that GROUP BY groups by the first in the table, so how can I get around doing this?

That's not actually true. It will return a random row. In practice, this is often the first row in primary key order, but there is no guarantee. Databases other than MySQL do not allow this practice and will raise an error.

Anyway, one way to retrieve the highest ID of unread messages is by joining the messages table twice. The second join only looks for unread messages. For example:

SELECT  max(msg.ID) as MaxID
,       messages.from
,       max(unread.ID) as MaxUnreadID
,       users.userName
FROM    users
join    messages msg
on      users.ID = msg.from
        and messages.to = ?
left join    
        messages unread
on      users.ID = unread.from
        and messages.to = ?
        and unread.read = 0
GROUP BY
        users.ID
,       users.userName