Chat table

id from_id to_id message time 1 1 3 Привет 05.01.2017 20:00 2 2 1 Здраствуй 05.01.2017 21:00 3 3 1 Привет! 05.01.2017 22:00

Table users

 id name 1 Алексей 2 Дмитрий 3 Ольга

I want to withdraw in the account of Alexei likeness:

 3 Ольга: Послднее сообщение 05.01.2017 22:00 2 Дмитрий: Послднее сообщение 05.01.2017 21:00

From patients who stupidly minus the question, there is no sense ..

I figured it out myself:

 SELECT DISTINCT to, MAX(time) FROM CHAT where from = '"1"' GROUP BY to ORDER BY MAX(time) DESC)

We receive a new message list without repeating the distance, you can already display it with names.

Here is the personal message of Alexai: enter image description here

Closed due to the fact that the essence of the question is incomprehensible by the participants Alexey Shimansky , AK ♦ , Vadim Ovchinnikov , user194374, Yuri 6 Jan '17 at 12:53 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • And if the last message in the chat with Olga is written by Alexey? And if with some Ivan (id = 4), this Alexey did not chat? And if this is the idiosyncrasy page, which generally hates chats and never answers? - Akina

1 answer 1

It is very difficult to guess what you need, but if you want for the owner of a personal account to receive a list of recent messages that are addressed to him, here is the SQL, which will be your starting point:

 SELECT [user_from].[id] ,[user_from].name ,' последнее сообщение ' ,[time] FROM [chat], [users] user_to, [users] user_from where [chat].[to_id] = [user_to].[id] AND user_to.name = 'Алексей' AND [chat].[from_id] = [user_from].[id] AND [time] = (SELECT MAX(time) FROM [chat] chat1 WHERE chat1.from_id = chat.from_id);

This query will be executed in any database with support for standard SQL. If that is not clear - ask. Just in case a useful link is http://www.w3resource.com/sql/aggregate-functions/max-date.php

  • one
    If you "minus" the answer, then it would be logical to indicate the reason, as it seems to me - Alexander Muksimov
  • I can assume that the minus is set for the fact that the mysql tag is attached to the question, and you have a quoting of names like for Access / MS SQL - Akina
  • Before uploading a solution, you need to check it, and during my holidays only MS SQL is available. I deliberately used in the query, only what is in all the SQL dialects, so that the principle was clear. Obviously, this query can be given a more modern look, but this is already “ryushechki”. - Alexander Muksimov