CODEWITHSUNDEEP
sqlmysql
fischer
fischer

Reputation: 269

MySQL GROUP BY/ORDER BY problem

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

Upvotes: 3

Views: 3280

Answers (2)

Andomar
Andomar

Reputation: 238096

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

Upvotes: 3

Wowa
Wowa

Reputation: 1811

i don't exactly understand the meaning of the the sql, but you could use a subslect without group and group afterwords in the main select. try this:

SELECT 
    *
FROM
    (
        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 = ?
        ORDER BY
            messages.read ASC,
            maxID DESC
    ) AS msg
GROUP BY
   msg.from

Upvotes: 0

Related Questions