List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 25 2002 7:05pm
Subject:Re: Selecting the row with largest number in a column
View as plain text  
At 17:13 +0000 1/26/02, DL Neil wrote:
>Hello Richard,
>
>
>>  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:
>
>SELECT *
>   FROM contacts
>   ORDER BY age DESC
>   LIMIT 1
>
>Regards,
>=dn

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;
Thread
Re: [PHP-DB] any good web hosting with php and mysql support?clint 14 Jan
  • Re: [PHP-DB] any good web hosting with php and mysql support? (OT)Mikel King14 Jan
  • Re: [PHP-DB] any good web hosting with php and mysql support? (OT) Vlasyuk Valera14 Jan
RE: [PHP-DB] any good web hosting with php and mysql support?Rick Emery14 Jan
RE: [PHP-DB] any good web hosting with php and mysql support?NCI/IMS)14 Jan
  • RE: [PHP-DB] any good web hosting with php and mysql support?Shannon Kendrick14 Jan
    • Re: [PHP-DB] any good web hosting with php and mysql support? Vlasyuk Valera14 Jan
      • Selecting the row with largest number in a columnRichard Morton25 Jan
        • Re: Selecting the row with largest number in a columnJoseph Bueno25 Jan
        • Re: Selecting the row with largest number in a columnDL Neil25 Jan
          • Re: Selecting the row with largest number in a columnPaul DuBois25 Jan
          • RE: Selecting the row with largest number in a columnButch Bean25 Jan
            • Re: Selecting the row with largest number in a columnDL Neil26 Jan
            • RE: Selecting the row with largest number in a columnRichard Morton28 Jan
              • RE: Selecting the row with largest number in a columnButch Bean28 Jan
                • RE: Selecting the row with largest number in a columnPaul DuBois28 Jan
                  • RE: Selecting the row with largest number in a columnButch Bean28 Jan
                  • RE: Selecting the row with largest number in a columnButch Bean28 Jan
RE: [PHP-DB] any good web hosting with php and mysql support?Rick Emery14 Jan
RE: [PHP-DB] any good web hosting with php and mysql support?Boaz Yahav17 Jan