ORDER BY 日期和时间 BEFORE GROUP BY 在 mysql 中的名称

ORDER BY date and time BEFORE GROUP BY name in mysql

提问by CodingYourLife

i have a table like this:

我有一张这样的桌子：

name    date         time
tom | 2011-07-04 | 01:09:52
tom | 2011-07-04 | 01:09:52
mad | 2011-07-04 | 02:10:53
mad | 2009-06-03 | 00:01:01

i want oldest name first:

我首先要最老的名字：

SELECT * 
ORDER BY date ASC, time ASC 
GROUP BY name

(->doesn't work!)

（-> 不起作用！）

now it should give me first mad(has earlier date) then tom

现在它应该先让我生气（有更早的约会）然后是汤姆

but with GROUP BY name ORDER BY date ASC, time ASCgives me the newer mad first because it groups before it sorts!

但是 with 首先GROUP BY name ORDER BY date ASC, time ASC给了我更新的 mad 因为它在排序之前先分组！

again: the problem is that i can't sort by date and time before i group because GROUP BY must be before ORDER BY!

再次：问题是我无法在分组之前按日期和时间排序，因为 GROUP BY 必须在 ORDER BY 之前！

回答by swbeeton

Another method:

另一种方法：

SELECT * 
FROM (
    SELECT * FROM table_name
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected.

GROUP BY 对它命中的第一个匹配结果进行分组。如果第一个匹配的命中恰好是您想要的，那么一切都应该按预期进行。

I prefer this method as the subquery makes logical sense rather than peppering it with other conditions.

我更喜欢这种方法，因为子查询具有逻辑意义，而不是用其他条件来填充它。

回答by Vincent

As I am not allowed to comment on user1908688's answer, here a hint for MariaDB users:

由于我不允许对 user1908688 的回答发表评论，这里给 MariaDB 用户一个提示：

SELECT *
FROM (
     SELECT *
     ORDER BY date ASC, time ASC
     LIMIT 18446744073709551615
     ) AS sub
GROUP BY sub.name

https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

回答by Cyril Gandon

I think this is what you are seeking :

我认为这就是你正在寻找的：

SELECT name, min(date)
FROM myTable
GROUP BY name
ORDER BY min(date)

For the time, you have to make a mysql date via STR_TO_DATE :

目前，您必须通过 STR_TO_DATE 创建一个 mysql 日期：

STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s')

So :

所以 ：

SELECT name, min(STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s'))
FROM myTable
GROUP BY name
ORDER BY min(STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s'))

回答by jokermt235

This worked for me:

这对我有用：

SELECT *
FROM your_table
WHERE id IN (
    SELECT MAX(id)
    FROM your_table
    GROUP BY name
);

回答by Bohemian

Use a subselect:

使用子选择：

select name, date, time
from mytable main
where date + time = (select min(date + time) from mytable where name = main.mytable)
order by date + time;

回答by Ben

Another way to solve this would be with a LEFT JOIN, which could be more efficient. I'll first start with an example that considers only the date field, as probably it is more common to store date + time in one datetime column, and I also want to keep the query simple so it's easier to understand.

解决这个问题的另一种方法是使用 a LEFT JOIN，这可能更有效。我将首先从一个仅考虑日期字段的示例开始，因为在一个日期时间列中存储日期 + 时间可能更为常见，而且我还希望保持查询简单以便更容易理解。

So, with this particular example, if you want to show the oldestrecord based on the datecolumn, and assuming that your table name is called peopleyou can use the following query:

因此，对于这个特定示例，如果您想根据日期列显示最旧的记录，并假设您的表名被调用，您可以使用以下查询：people

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date > p2.date
WHERE p2.date is NULL
GROUP BY p.name

What the LEFT JOINdoes, is when the p.datecolumn is at its minimum value, there will be no p2.datewith a smaller value on the left join and therefore the corresponding p2.datewill be NULL. So, by adding WHERE p2.date is NULL, we make sure to show only the records with the oldest date.

什么LEFT JOIN呢，就是当p.date列在其最小值，也就没有p2.date与左边的较小值加入，因此相应的p2.date会NULL。因此，通过添加WHERE p2.date is NULL，我们确保仅显示日期最早的记录。

And similarly, if you want to show the newestrecord instead, you can just change the comparison operator in the LEFT JOIN:

同样，如果您想显示最新的记录，您只需更改 中的比较运算符LEFT JOIN：

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date < p2.date
WHERE p2.date is NULL
GROUP BY p.name

Now, for this particular example where date+time are separate columns, you would need to add them in some way if you want to query based on the datetime of two columns combined, for example:

现在，对于这个日期+时间是单独列的特定示例，如果要根据两列组合的日期时间进行查询，则需要以某种方式添加它们，例如：

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date + INTERVAL TIME_TO_SEC(p.time) SECOND > p2.date + INTERVAL TIME_TO_SEC(p2.time) SECOND
WHERE p2.date is NULL
GROUP BY p.name

You can read more about this (and also see some other ways to accomplish this) on the The Rows Holding the Group-wise Maximum of a Certain Columnpage.

您可以在The Rows Holding the Group-wise Maximum of a Specific Column页面上阅读更多相关信息（也可以查看其他一些实现此目的的方法）。

回答by Max

In Oracle, This work for me

在 Oracle 中，这对我有用

SELECT name, min(date), min(time)
    FROM table_name
GROUP BY name

回答by SirajuddinLuck

work for me mysql select * from (SELECT number,max(date_added) as datea FROM sms_chat group by number) as sup order by datea desc

为我工作 mysql select * from (SELECT number,max(date_ added) as datea FROM sms_chat group by number) as sup order by datea desc

回答by Absolute?ER?

I had a different variation on this question where I only had a single DATETIMEfield and needed a limitafter a group byor distinctafter sorting descending based on the datetimefield, but this is what helped me:

我在这个问题上有一个不同的变体，我只有一个DATETIME字段，并且需要一个limitafter agroup by或distinctafter 根据datetime字段降序排序，但这对我有帮助：

select distinct (column) from
(select column from database.table
order by date_column DESC) as hist limit 10

In this instance with the split fields, if you can sort on a concat, then you might be able to get away with something like:

在这种带有拆分字段的实例中，如果您可以对 concat 进行排序，那么您可能会得到类似的结果：

select name,date,time from
(select name from table order by concat(date,' ',time) ASC)
as sorted

Then if you wanted to limit you would simply add your limit statement to the end:

然后，如果您想限制，只需将限制语句添加到末尾：

select name,date,time from
(select name from table order by concat(date,' ',time) ASC)
as sorted limit 10

回答by Satys

This is not the exact answer, but this might be helpful for the people looking to solve some problem with the approach of ordering row before group by in mysql.

这不是确切的答案，但这可能对那些希望通过在 mysql 中按组排序之前的方法来解决某些问题的人有所帮助。

I came to this thread, when I wanted to find the latest row(which is order by date descbut get the only one result for a particular column type, which is group by column name).

我来到这个线程，当我想找到最新的行（这order by date desc只是获得特定列类型的唯一结果，即group by column name）。

One other approach to solve such problem is to make use of aggregation.

解决此类问题的另一种方法是使用聚合。

So, we can let the query run as usual, which sorted ascand introduce new field as max(doc) as latest_doc, which will give the latest date, with grouped by the same column.

因此，我们可以让查询照常运行，按 asc 排序并引入新字段 as max(doc) as latest_doc，这将给出最新日期，并按同一列分组。

Suppose, you want to find the data of a particular column now and max aggregationcannot be done. In general, to finding the data of a particular column, you can make use of GROUP_CONCAT aggregator, with some unique separator which can't be present in that column, like GROUP_CONCAT(string SEPARATOR ' ') as new_column, and while you're accessing it, you can split/explode the new_column field.

假设您现在要查找特定列的数据并且无法进行最大聚合。一般来说，要查找特定列的数据，您可以使用GROUP_CONCAT aggregator, 以及该列中不存在的一些唯一分隔符，例如GROUP_CONCAT(string SEPARATOR ' ') as new_column，并且在访问它时，您可以拆分/分解 new_column 字段.

Again, this might not sound to everyone. I did it, and liked it as well because I had written few functions and I couldn't run subqueries. I am working on codeigniter framework for php.

同样，这可能不是每个人都听过的。我做到了，也很喜欢它，因为我编写的函数很少，而且我无法运行子查询。我正在研究 php 的 codeigniter 框架。

Not sure of the complexity as well, may be someone can put some light on that.

也不确定复杂性，可能有人可以对此有所了解。

Regards :)

问候 ：）