There is a message database with structure

id - ID user_id - ID отправившего сообщение owner_id - ID получившего сообщение text - текст сообщения created - дата создания в формате Ymd H:i:s

It is necessary to obtain the last 10 entries from the database grouped by 2 fields user-id and owner-id , sorted by date.

Data in the database:

 id | user_id | owner_id | text | created ------------------------------------------------------------------- 1 | 1 | 2 | Привет Коля | 2000-01-01 00:00:01 2 | 2 | 1 | Здорова Кирилл | 2000-01-01 00:00:02 3 | 2 | 1 | Как дела? | 2000-01-01 00:00:03 4 | 1 | 3 | Привет Серега! | 2000-01-01 00:00:04 5 | 3 | 1 | ООО Кирюха привет! | 2000-01-01 00:00:05 6 | 2 | 3 | От Коли - Сергею | 2000-01-01 00:00:06 7 | 3 | 1 | Есть денег в долг? | 2000-01-01 00:00:07 8 | 1 | 4 | Ну что у нас? | 2000-01-01 00:00:08

When sampling, I want to get this result:

 id | user_id | owner_id | text | created ------------------------------------------------------------------- 8 | 1 | 4 | Ну что у нас? | 2000-01-01 00:00:08 7 | 3 | 1 | Есть денег в долг? | 2000-01-01 00:00:07 3 | 2 | 1 | Как дела? | 2000-01-01 00:00:03

How to do?

(date January 1, 2000 midnight - for clarity)

The solution is

 'SELECT id, user_id, owner_id, text, created, (user_id + owner_id) uid FROM (SELECT * FROM messages ORDER BY created DESC) AS msg WHERE user_id = :user_id OR owner_id = :user_id GROUP BY uid ORDER BY created DESC LIMIT :limit'

    1 answer 1

      SELECT id, user_id, owner_id, text, created FROM messages GROUP BY user_id, owner_id ORDER BY created DESC LIMIT 10;
    • If this is done, then SQL gives me all the possible options. User_id - owner_id 1 - 2 2 - 1 3 - 1 1 - 3 and it is necessary that both sides be unique. However, the order owner_id and user_id should not matter - Chetson
    • I figured out how to display the unique values ​​of both fields. We add owner_id and user_id. We get the UID. The unique ID of the messages, grouped by UID. We receive messages in a Single copy, but now the point is that the Group chooses the FIRST value and it is necessary LAST - Chetson
    • Yeah. 1 + 4 and 2 + 3 ... Now I will think over the first item. Now it’s more clear what exactly you wanted. 1 field where user_id or owner_id = 1 - not very obvious from the question. - Denis Khvorostin
    • 'SELECT id, user_id, owner_id, text, created, (user_id + owner_id) uid FROM messages WHERE user_id = :user_id OR owner_id = :user_id GROUP BY uid ORDER BY created DESC LIMIT :limit' group by UID but I need to get the last message - Chetson