如何先排序ORDER BY id(先排序新消息)，然后排序GROUP BY thread_id

Question

首先，我非常感谢所有在这个话题上帮助我的人，感谢你们在这里度过的时间。对于这个小问题，请在下面找到我所有的研究，但从现在开始，我还没有找到正确的答案：

这些都是我用过的代码，但都没有让人满意：

$list_of_message = $bdd->prepare('
SELECT sender_id
, receiver_id
, message
, MAX(id) 
FROM private_message 
WHERE sender_id = ? 
OR receiver_id = ? 
GROUP 
BY thread_id 
ORDER 
BY id DESC
');

$list_of_message->execute(array($user_session_id, $user_session_id));

$fetch_list_of_message = $list_of_message->fetchAll();

或

[$list_of_message = $bdd->prepare('SELECT id, MAX(date_time) AS mhour FROM private_message GROUP BY thread_id ORDER BY mhour DESC');
$list_of_message->execute(array($user_session_id, $user_session_id));
$fetch_list_of_message = $list_of_message->fetchAll();][1]

或

$list_of_message = $bdd->prepare(' SELECT sender_id, receiver_id, message  FROM (SELECT * FROM private_message ORDER BY id DESC) AS sub WHERE sender_id = 42 OR receiver_id = 42 GROUP BY thread_id LIMIT 5');
$list_of_message->execute();
$fetch_list_of_message = $list_of_message->fetchAll(PDO::FETCH_ASSOC);

这是我的数据库：

https://ibb.co/d4nxbvw

结果是：

https://ibb.co/fYw3ydT

正如您所看到的，GROUP BY可以很好地工作，但SORT BY则不然。

事实上，消息id 4在GROUP BY中总是领先于消息id 6。然而，您可以在数据库中看到，消息编号6具有最高的id，这就是所有这些演示的要点：

你能帮我先订购BY id吗(首先有消息编号6(，然后是GROUP BY thread_id 吗

我会继续从我的身边搜索

附言：请在下面找到一位医生，他可能会帮助一些有同样问题的人：https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

非常感谢你的帮助

Answer 1

试试这个SQL查询，

SELECT max(id) as id, sender_id, receiver_id, message
FROM private_message WHERE sender_id=42 or receiver_id=42 
GROUP by thread_id 
ORDER by id DESC
LIMIT 5;