CODEWITHSUNDEEP
mysqlperformance
Sundar
Sundar

Reputation: 4650

How to select a maximum value row in mysql table

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?

Upvotes: 19

Views: 48473

Answers (7)

ghost28147
ghost28147

Reputation: 558

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

SELECT * FROM people ORDER BY age DESC LIMIT 1

Upvotes: 11

Marco
Marco

Reputation: 209

There is an interesting alternative that works only on 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

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

Upvotes: 0

lhrec_106
lhrec_106

Reputation: 630

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);

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

Upvotes: 0

Nisarg
Nisarg

Reputation: 3272

try this

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

Upvotes: 11

Bugs
Bugs

Reputation: 1452

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 index on age so the performance isn't affected as the table grows.

Upvotes: 0

C3roe
C3roe

Reputation: 96382

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

Upvotes: 3

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

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);

Upvotes: 22

Related Questions