Получить последний ряд в каждой группе?

Question

Здравствуйте, в основном я пытаюсь скопировать систему обмена сообщениями, что Facebook имеет на моем сайте.

Это логика... "Когда пользователь1 СОЗДАЕТ НОВОЕ СООБЩЕНИЕ для отправки пользователю7, создается новый поток с идентификатором потока равным 1(таблица: messages_thread), и в таблицу добавляется новая запись: сообщения, равные message_id 1(таблица).: messages). Когда user7 ОТВЕТИТ на сообщение user1, создается message2, а для thread_id он равен 1.

Теперь, когда пользователь 1 СОЗДАЕТ НОВОЕ СООБЩЕНИЕ для отправки пользователю 7, создается поток 2 и создается сообщение 3. Когда пользователь 7 отвечает на тему 2, создается сообщение 4 (надеюсь, вы понимаете логику).

Все отлично. Единственная проблема заключается в том, что мне нужно выбрать самое новое сообщение в теме, но у меня возникли проблемы с SQL,

Этот sql, который у меня есть на данный момент...

SELECT max(message_id) message_id, m.thread_id, m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10

Производит это...

+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
| message_id | thread_id | body                 | user_id | to_id | message_status | new | date       | id | displayname  | username | profile_img |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
|          6 |         2 | Really nice          |       1 |     7 | read           |   0 | 1298617367 |  1 | Kenny  Blake | imkenee  | 28_1        |
|          4 |         1 | Whats good with you? |       1 |     7 | read           |   0 | 1298607438 |  1 | Kenny  Blake | imkenee  | 28_1        |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+

Это хорошо, но одна небольшая проблема, он выбирает первый ряд в каждой группе, и я пытаюсь выбрать самый новый (последний ряд) в каждой группе

Как я могу это сделать? вот таблицы. Спасибо!

Таблица: сообщения

+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
| id | user_id | subject        | from_status | to_status | from_s_delete | to_s_delete | date       |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
|  1 |       1 | Hey Kenny      | unread      | unread    |             0 |           0 | 1298607438 |
|  2 |       7 | Check out this | unread      | unread    |             0 |           0 | 1298617344 |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+

Табличные сообщения

+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
| message_id | thread_id | user_id | to_id | body                                                      | message_status | is_sent_deleted | new | date       |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
|          1 |         1 |       1 |     7 | Whats good with you?                                      | read           |               0 |   0 | 1298607438 |
|          2 |         1 |       7 |     1 | Nothing Kenny just chilling. Whats up with you though???? | read           |               0 |   0 | 1298607473 |
|          4 |         1 |       1 |     7 | Just posted victor how are you man?                       | read           |               0 |   0 | 1298607956 |
|          5 |         2 |       7 |     1 | Look at this poem....                                     | read           |               0 |   0 | 1298617344 |
|          6 |         2 |       1 |     7 | Really nice                                               | read           |               0 |   0 | 1298617367 |
|          7 |         2 |       7 |     1 | Yea i know right :)                                       | unread         |               0 |   0 | 1298617383 |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+

Answer 1

Сгруппируйте потоки в подзапросе, который возвратит последнее сообщение для каждого потока:

SELECT m.message_id, m.thread_id, m.body, m.user_id,
    m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m 
  INNER JOIN users u ON u.id = m.user_id
  INNER JOIN (
    SELECT MAX(message_id) MaxMsgIDForThread
    FROM messages
    WHERE to_id = 7
      AND (message_status = 'unread'
        or message_status='read'
        or message_status='saved') 
    GROUP BY thread_id
  ) g ON m.message_id = g.MaxMsgIDForThread
Order by m.message_id Desc
LIMIT 10

WHERE возможно, потребуется переместить его во внешний запрос, сейчас он выберет последнее сообщение, соответствующее критериям, переместит его во внешний запрос, если вы хотите полностью пропустить поток, если условия не выполняются.

Вы должны также рассмотреть вопрос о сохранении статуса сообщения как ENUM который поможет сравнения.

Answer 2

Нет возможности добавить порядок в группу по
Но, возможно, это работает:

SELECT max(message_id) message_id, MAX(m.thread_id), m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10