How to place ORDER BY in INNER JOIN/GROUP BY

Asked

Viewed 390 times

0

In a chat, I need to give a SELECT in the contacts of the logged in user, but ordering them by the date of the messages The code is like this: 

SELECT contatos.*, contas.Key_User, contas.Login_User, chat.Date 
FROM contatos INNER JOIN contas 
ON (contatos.Key_User1 = contas.Key_User) 
INNER JOIN chat 
ON (contatos.Key_User1 = chat.Key_User1 OR contatos.Key_User1 = chat.Key_User2) 
WHERE contas.Key_User not like 'valor'
GROUP BY contas.Key_User 
ORDER BY chat.Date DESC

And the result is this: Resultado do SELECT. But the chat. Date shown is the first message, and it doesn’t work because to sort by recent date, it has to be just the opposite. I wanted to know how to make GROUP BY pick up by the last message.

mysql sql

  • From what I understand the problem is in the chat, and not in the return of the bank? because in the bank it shows first the last message, because this with DESC after ORDER BY, what is the problem in chat? could not be his code

  • The thing is, when I give Select without GROUP BY, it shows all DESC messages in the chat. Date, perfect, but making the same contact appear several times for each message you have. With GROUP BY, it only appears once the contact, but the problem is that it uses the chat.Date of the first record of the message, so it will use as a sort parameter the date of the first message, not the last one.

  • 1

    Got it! I replaced chat.Date with max(chat.Date) and went.

  • @Erickgermani put his solution as answer then, to help other people and be able to close the question.

  • I removed the php tag because there is no need to relate a single variable to the php language. This will broaden the horizon of people to help you, believe me.

1 answer

1


From what I understand, you want to list users based on the date of the last message, try using with a sub-query, follow example:

    SELECT 
         contatos.*, 
         contas.Key_User, 
         contas.Login_User,
         (
             SELECT
                 chat.Date
             FROM 
                 chat
             WHERE
                  (contatos.Key_User1 = chat.Key_User1 OR contatos.Key_User1 = chat.Key_User2)
             ORDER BY 
                  chat.Date DESC 
             LIMIT 1
         )  data_ultima_mensagem
   FROM contatos 
         INNER JOIN contas  ON (contatos.Key_User1 = contas.Key_User) 
   WHERE contas.Key_User not like 'valor'
   ORDER BY 
       data_ultima_mensagem
    ;

In this case, I did a sub-query only to find out the date of the Last message. It is therefore not necessary to use group by.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.