使用group by时按日期时间的SQL顺序(SQL order by datetime when using group by)

使用group by时按日期时间的SQL顺序(SQL order by datetime when using group by)

我正在使用SQL查询按字段分组

SELECT * from text_messages group by number order by datetime DESC

哪个工作正常，但它没有显示每个group by的最新列

例如，我有2行具有相同的数字和以下日期时间：

2015-11-01 10:43:39 2015-10-31 22:50:17

所以它只显示其中一个是2015-10-31 22:50:17

我怎样才能让它显示最新的专栏，而不是耳朵？

I am using an SQL Query to group by a field

SELECT * from text_messages group by number order by datetime DESC

which works fine, however its not showing the latest column for each group by

for example, i have 2 rows with the same number and the following datetime:

2015-11-01 10:43:39 2015-10-31 22:50:17

so its only showing one of these which is the 2015-10-31 22:50:17

how can i make it show the latest column and not the earlies?

最满意答案

使用MAX聚合函数：

SELECT `number`, MAX(`datetime`) AS `datetime` FROM text_messages GROUP BY `number` ORDER BY `datetime` DESC;

另请注意， datetime是关键字，应该重命名，或者需要用反引号引用它。

SqlFiddleDemo

Use MAX aggregate function:

SELECT `number`, MAX(`datetime`) AS `datetime` FROM text_messages GROUP BY `number` ORDER BY `datetime` DESC;

Also note that datetime is keyword and it should be renamed or you need to quote it with backticks.

SqlFiddleDemo