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?
Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?
Question 1 : What I made mistake here and why this MAX function is not return the relevant row information?
You need to read up on the group by
clause.
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 by
clause. 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.
Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?
Your first statement (using max()
without a group by
) is simply incorrect.
If you want one of the oldest users, order by age desc limit 1
is the correct way to proceed.
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);
try this
SELECT age, name FROM `people` where age = (SELECT max(age) FROM people)
Yet another solution with no use of agregate functions and groupings:
SELECT * FROM people ORDER BY age DESC LIMIT 1
What I made mistake here and why this MAX function is not return the relevant row information?
MAX is returning the correct value - but the other column you select just gives you a "random" value.
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.
Alternative approaches are described here: The Rows Holding the Group-wise Maximum of a Certain Column
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With