Problème MySQL GROUP BY/ORDER BY

Question

J'ai une requête MySQL comme celle-ci :

   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

Bien que cela fonctionne bien et que les messages soient groupés par l'utilisateur qui les a envoyés dans l'ordre DESCENDANT, j'ai maintenant un problème lorsque je veux ORDER BY la ligne messages.read. Mon code est le suivant :

   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

Maintenant, messages.read renvoie la valeur de la ligne du premier message que l'utilisateur a envoyé. Mais j'ai besoin de la valeur du plus récent (ID le plus élevé) que l'utilisateur a envoyé. Je sais que GROUP BY regroupe le premier dans la table, alors comment puis-je contourner ce problème ?

Merci beaucoup d'avance, Fischer

Answer 1

Je sais que GROUP BY regroupe par le premier dans le tableau, alors comment puis-je contourner ce problème ?

Ce n'est pas vraiment vrai. Il renverra une ligne aléatoire. En pratique, il s'agit souvent de la première ligne dans l'ordre de la clé primaire, mais il n'y a aucune garantie. Les bases de données autres que MySQL n'autorisent pas cette pratique et génèrent une erreur.

Quoi qu'il en soit, une façon de récupérer l'ID le plus élevé des messages non lus est de joindre deux fois la table des messages. La deuxième jointure ne recherche que les messages non lus. Par exemple :

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

Answer 2

Je ne comprends pas exactement le sens du sql, mais vous pourriez utiliser un subslect sans groupe et grouper après dans le select principal. essayez ceci :

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