MySQL 如何在mysql表中选择最大值行

How to select a maximum value row in mysql table

提问by Sundar

I have the following table

我有下表

Table structure:

表结构：

CREATE TABLE IF NOT EXISTS `people` ( 
`name` varchar(10) NOT NULL, 
`age` smallint(5) unsigned NOT NULL, 
PRIMARY KEY (`name`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

Insert some values:

插入一些值：

INSERT INTO `people` (`name`, `age`) VALUES 
('bob', 13), 
('john', 25), 
('steve', 8), 
('sue', 13); 

Executed Query:

执行查询：

SELECT MAX(  `age` ) ,  `name` FROM  `people` WHERE 1

Expected Result:

预期结果：

25, John

Generated Result

生成结果

25, bob

We can achieve this by using this query

我们可以通过使用这个查询来实现这一点

SELECT `age`,  `name` FROM  `people` ORDER BY age DESC LIMIT 1

Question 1 : What I made mistake here and why this MAX function is not return the relevant row information?

问题 1：我在这里犯了什么错误，为什么这个 MAX 函数没有返回相关的行信息？

Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?

问题2：MAX函数或ORDER BY子句哪个好用，提高性能？

回答by Denis de Bernardy

Question 1 : What I made mistake here and why this MAX function is not return the relevant row information?

问题 1：我在这里犯了什么错误，为什么这个 MAX 函数没有返回相关的行信息？

You need to read up on the group byclause.

您需要仔细阅读该group by条款。

MySQL is being a lot more permissive than it should, introducing confusion in the process. Basically, any column without an aggregate should be included in the group byclause. But MySQL syntactic sugar allows to "forget" columns. When you do, MySQL spits out an arbitrary value from the set that it's grouping by. In your case, the first row in the set is bob, so it returns that.

MySQL 比它应有的宽松得多，在此过程中引入了混乱。基本上，任何没有聚合的列都应该包含在group by子句中。但是 MySQL 语法糖允许“忘记”列。当你这样做时，MySQL 从它分组的集合中吐出一个任意值。在您的情况下，集合中的第一行是bob，因此它返回。

Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?

问题2：MAX函数或ORDER BY子句哪个好用，提高性能？

Your first statement (using max()without a group by) is simply incorrect.

您的第一条语句（max()不带 a使用group by）完全不正确。

If you want one of the oldest users, order by age desc limit 1is the correct way to proceed.

如果您想要最老的用户之一，order by age desc limit 1则是正确的方法。

If you want all of the oldest users, you need a subselect:

如果你想要所有最老的用户，你需要一个子选择：

SELECT p.* FROM people p WHERE p.age = (select max(subp.age) from people subp);

回答by Napster

try this

尝试这个

SELECT age, name FROM  `people` where age = (SELECT max(age) FROM  people)

回答by ghost28147

Yet another solution with no use of agregate functions and groupings:

另一个不使用聚合函数和分组的解决方案：

SELECT * FROM people ORDER BY age DESC LIMIT 1

SELECT * FROM people ORDER BY age DESC LIMIT 1

回答by CBroe

What I made mistake here and why this MAX function is not return the relevant row information?

我在这里犯了什么错误，为什么这个 MAX 函数没有返回相关的行信息？

MAX is returning the correct value - but the other column you select just gives you a "random" value.

MAX 返回正确的值 - 但您选择的另一列只是给您一个“随机”值。

Selecting columns that are not part of the GROUPing (and I think implicit GROUP BY is done here since you use an aggregate function) is illegal in strict SQL - MySQL however ignores that (depending on server config), and gives you a value from a "random" row in such cases.

选择不属于 GROUPing 的列（并且我认为隐式 GROUP BY 在这里完成，因为您使用聚合函数）在严格的 SQL 中是非法的 - 但是 MySQL 会忽略它（取决于服务器配置），并为您提供一个值在这种情况下，“随机”行。

---

---

Alternative approaches are described here: The Rows Holding the Group-wise Maximum of a Certain Column

此处描述了替代方法：保存特定列的分组最大值的行

回答by Bugs

MAX is an aggregate function. That means MySQL groups all of the records and treats them as if they were one in the result set. Since you doesn't state how the name column is to be grouped, the result of that may be unexpected. ORDER BY is a perfectly fine way to achieve the desired result. Just don't forget to add an indexon age so the performance isn't affected as the table grows.

MAX 是一个聚合函数。这意味着 MySQL 将所有记录分组并将它们视为结果集中的一个记录。由于您没有说明如何对名称列进行分组，因此结果可能出乎意料。ORDER BY 是实现所需结果的完美方式。只是不要忘记在 age上添加一个索引，这样性能就不会随着表的增长而受到影响。

回答by lhrec_106

Question 1 : If you really want to use the MAX() you could try this SELECT age, name FROM people WHERE age IN (SELECT MAX(age) FROM people);

问题 1：如果你真的想使用 MAX() 你可以试试这个 SELECT age, name FROM people WHERE age IN (SELECT MAX(age) FROM people);

Question 2: I think it depends, for my suggestion in question 1, you do the query twice, but in the ORDER BYsolution you provided, the database performed a sort like procedure.

问题 2：我认为这取决于我在问题 1 中的建议，您进行了两次查询，但是在ORDER BY您提供的解决方案中，数据库执行了类似过程的排序。

回答by Marco

There is an interesting alternative that works only on MySql!

有一个有趣的替代方案仅适用于 MySql！

SELECT `Name`, `Age` FROM 
(SELECT `Name`, `Age`, 1 AS `foo`
FROM `People`
ORDER BY `Age` DESC) AS `x`
GROUP BY `foo`

This works because MySql returns the first row, when no aggregation function is applied on a given column

这是有效的，因为 MySql 返回第一行，当没有对给定列应用聚合函数时

Here is the link: http://tominology.blogspot.com.br/2014/10/sql-row-with-max-value.html

这是链接：http: //tominology.blogspot.com.br/2014/10/sql-row-with-max-value.html