At 17:13 +0000 1/26/02, DL Neil wrote:
>> I have a simple query, and a problem countless people must have had, I just
>> cannot work it out at the moment, I am new to MySQL; I hope you can help.
>> My current statement looking at the manual.
>> SELECT * FROM contacts WHERE age=MAX(age);
>> I started with:
>> mysql> select * from contacts where age=(select MAX(age) from contacts);
>> In escence I am trying to ascertain the details of the person who is oldest.
>> Any suggestions.?
>Use the ORDER BY clause to show the table's records in inverted age
>order, then require only the first row:
> FROM contacts
> ORDER BY age DESC
> LIMIT 1
Another method, which will give you different results if more than one
row has the maximum values (and which may be desireable if you want to see
all such rows rather than just one) is to use a SQL variable like this:
SELECT @max := MAX(age) FROM contacts;
SELECT * FROM contacts where age = @max;