MAX with LIMIT does not give the correct result

Asked

Viewed 131 times

0

Imagine the following lines in my Mysql database:

Exemplo de dados da tabela

I want a query that displays the name and age of the person who has the highest age in the table, for that 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 name the age returned by the command MAX, I didn’t want an understatement that compares age to age MAX(idade) another query. There is this I need?

mysql query select-sql
  • 1

    What name do you want me to return? What’s going wrong? And why don’t you want an undercurrent?

  • Cannot group by name?

  • 1

    Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

4 answers

3

The subconsulta is the default solution and I would go in it. But if you want to insist on another one it could be this one (which could be worse):

SELECT nome, idade FROM pessoas ORDER BY idade DESC LIMIT 1;

If you do not have a suitable index you can be quite slow, of course, not slower than the sub-consumption under the same conditions. There is nothing miraculous.

If you have a tie in age will take one of them, probably the first one that was inserted, the question does not specify tie-breaker criteria or if you should take all of the same age, which would cause an under-consumption anyway.

Just to complete the answer, I know you don’t want to (I don’t know the reason), but for other users who want to do the sub-sale.

SELECT nome, idade FROM pessoas WHERE idade = (SELECT MAX(idade) FROM pessoas);

I put in the Github for future reference.

2

They have already presented the best solutions. But you can solve only 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:

  • take all people and, associated with them, older people
  • remove all pairs in whom it was possible to determine someone older

I don’t know the performance impact of this solution, but there have been cases where it was easier to use it than trying a subselect here at work. Use sparingly and wisely, however.

1

It is that when you assign a MAX(age) column it will bring only the oldest age column in all search lines.

Following the Maniero response line, you can select only one record of the older person (but you may have others of the same age in the bank):

SELECT nome, idade FROM pessoas ORDER BY idade DESC LIMIT 1;

Or, use an subconsultation, which may be much slower, but will bring all the records of those who have the highest age registered:

SELECT nome, idade FROM pessoas WHERE idade = (SELECT MAX(idade) FROM pessoas) ORDER BY nome ASC;

And as he himself mentioned, 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, for you could consult name and age bringing only all who are of age and ordering by name.

0

Do so:

SELECT nome, idade from pessoas WHERE idade = ( select max( idade ) from pessoas )

Browser other questions tagged mysql query select-sql

You are not signed in. Login or sign up in order to post.