mysql在组通过之前获得顺序

I have three tables and I will just show you the relevant columns here

我有三个表，我会在这里给你们看相关的列

Table: groups Columns: group_id, name.

表:组列:group_id，名称。

Table: groups_to_message Columns: group_id, message_id

表:groups_to_message列:group_id、message_id

Table: messages Columns: message_id, created (date)

表:消息列:message_id，已创建(日期)

I need to basically find the last message for each group, with out showing duplicate groups.

我需要找到每个组的最后一条消息，并显示重复的组。

I've tried using group by like this:

我试过用这样的组:

SELECT m.created, g.group_id 
FROM groupss as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m 
GROUP BY g.group_id 
ORDER BY m.created DESC

This causes the successful grouping but is done before the ORDER BY so the first result is taken before the sort.

这将导致成功的分组，但是在排序之前完成，因此在排序之前获取第一个结果。

Any help appreciated.

任何帮助表示赞赏。

3 个解决方案

#1

If I'm reading your question right, Use MAX(). This should get you the most recent item for each group

如果我没看错你的问题，请使用MAX()。这将为您提供每个组的最新项目。

Example...

例子……

SELECT MAX(m.created), g.group_id 
FROM groupss as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m ON (gm.message_id = gm.message_id)
GROUP BY g.group_id 
ORDER BY m.created DESC

3 个解决方案

#1

更多相关文章

随机推荐