当我选择MAX(updated_date)时,其他列的值错误 [英] Getting wrong values for other columns when I select MAX(updated_date)
问题描述
我正在使用select field1, max(updated_date) from mytable
.
我得到了max(updated_date)
的正确值,即最大日期.
但是,对于field1
,当我真的想要第三条记录(具有最大日期值的记录)中的"ta3"值时,我只获得第一条记录的值,即"ta1".
I am using select field1, max(updated_date) from mytable
.
I get the correct value for max(updated_date)
, i.e. the largest date.
However for field1
I just get the the value for the first record, i.e. "ta1" when I really want the "ta3" value from the third record (the one with the max date value).
例如
+------------+---------------------+
| field1 | update_date |
+------------+---------------------+
| ta1 | 2012-03-11 11:05:15 |
| ta2 | 2012-03-11 11:05:32 |
| ta3 | 2012-03-11 11:05:56 |
+------------+---------------------+
3 rows in set (0.00 sec)
+------------+---------------------+
| field1 | max(update_date) |
+------------+---------------------+
| ta1 | 2012-03-11 11:05:56 |
+------------+---------------------+
1 row in set (0.00 sec)
推荐答案
您需要GROUP BY子句或更复杂的查询.
You either need a GROUP BY clause or a more complex query.
SELECT field1, MAX(updated_date)
FROM mytable
GROUP BY field1
对于示例数据,这将返回3行.
For the sample data, this will return 3 rows.
您更有可能想要:
SELECT t1.field1, t3.max_date
FROM mytable AS t1
JOIN (SELECT MAX(t2.updated_date) AS max_date
FROM mytable AS t2
) AS t3
ON t1.updated_date = t3.max_date;
对于示例数据,这将返回1行:
For the sample data, this will return 1 row:
ta3 2012-03-11 11:05:56
主要DBMS中,只有MySQL允许省略GROUP BY子句当你在选择列表中的聚集体和非聚合列的混合物. SQL标准需要GROUP BY子句,并且您必须在其中列出所有非聚合列.有时,在MySQL中,省略GROUP BY子句会产生所需的答案;不过,它常常能够给出意外的答案.
Of the major DBMS, only MySQL allows you to omit the GROUP BY clause when you have a mixture of aggregates and non-aggregate columns in the select-list. The SQL standard requires the GROUP BY clause and you must list all non-aggregate columns in it. Sometimes, in MySQL, omitting the GROUP BY clause produces the answer you want; as often as not, though, it manages to give an unexpected answer.
这篇关于当我选择MAX(updated_date)时,其他列的值错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!