List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 28 2002 4:28pm
Subject:RE: Selecting the row with largest number in a column
View as plain text  
At 10:06 -0500 1/28/02, Butch Bean wrote:
>Richard,
>
>I use this with all kinds of situations.
>I am using this function and others like it on tables with 100k to 3m
>records.
>They always work best when the field your looking for info on is indexed.
>Order By only causes a second process that seems unnecessary but I have not
>tested it directly
>
>Keep in mind that when I do use max() I am only interested in the highest
>number, I don't care how many are the highest or what they are.  But...
>
>This will get you all or the record you are looking for using MySQL
>variables(I think someone else had this answer posted as well):
>
>SELECT @maxage:=max(age) from contacts;
>SELECT * FROM contacts WHERE age=@maxage;
>
>The above can be one call
>
>Perl Example:
>
>my $getbigage = $dbh->prepare(
>		"SELECT @maxage:=max(age) from contacts;".
>		"SELECT * FROM contacts WHERE age=@maxage;";

I don't believe this will work.  @ in double-quoted Perl strings must
be escaped with a backslash.  More important, you cannot issue multiple
queries at a time.  In the case above, the MySQL will return an error
indicating a syntax error at the semicolon that occurs within the string.

>.
>.
>$getbigage->execute();
>...$getbigage->fetchrow_array()...
>.
>
>I use VB and Perl and the examples I could give you can get lengthy
>
>This example would be very slow in comparison
>>  SELECT *
>>    FROM contacts
>>    ORDER BY age DESC
>>    LIMIT 1
>
>Butch Bean
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