Imagine the following lines in my MySQL database:
I want a query that displays the name and age of the person who is the oldest in the table, so I did this query :
SELECT nome, MAX(idade)
from pessoas;
But it does not return the name correctly.
I need a solution that conditions the age name returned by the MAX command, did not want a subquery that compares if the age with the MAX(idade) of another query. Is there what I need?
The subquery is the default solution and I would go into it. But if you want to insist on another one could be this (which can be worse):
SELECT nome, idade FROM pessoas ORDER BY idade DESC LIMIT 1;
If you do not have a suitable index, it can be quite slow, of course, not slower than the subquery under the same conditions. There is nothing miraculous.
If you have a tie in age you will get one of them, probably the first one that was inserted, the question does not specify tiebreaker criteria or if it should catch all of the same age, which would cause a subquery anyway.
Just to complete the answer, I know you do not want (I do not know the reason), but for other users who want to do the subquery.
SELECT nome, idade FROM pessoas WHERE idade = (SELECT MAX(idade) FROM pessoas);
They have presented the best solutions. But you can only solve with junctions your problem:
SELECT a1.nome, a1.idade
FROM pessoas a1 LEFT JOIN
pessoas a2 on a1.idade < a2.idade
WHERE a2.idade IS NULL
I got the idea of a LEFT JOIN WITH EXCLUSION . The general idea here is:
I do not know the performance impact of this solution, but had cases where it was easier to use than trying a subselect here at work. Use sparingly and wisely, though.
It is that when you assign a MAX (age) column it will only bring the highest age of the age column in all the rows of the search.
Following Maniero's response line, you can select only one record of the oldest person (but you can have others of the same age at the bank):
SELECT nome, idade FROM pessoas ORDER BY idade DESC LIMIT 1;
Or, use a subquery, which may be much slower, but will fetch all the records of those with the highest age:
SELECT nome, idade FROM pessoas WHERE idade = (SELECT MAX(idade) FROM pessoas) ORDER BY nome ASC;
And as he himself quoted, it is important that you have an index that contains the fields that you use in your query, in which case an index that has age and name could be very interesting because you could query name and age only all who are of age and ordaining by name.
Do this:
SELECT nome, idade from pessoas WHERE idade = ( select max( idade ) from pessoas )