MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Butch Bean Date:January 28 2002 3:06pm
Subject:RE: Selecting the row with largest number in a column
View as plain text  
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;";
.
.
$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


-----Original Message-----
From: Richard Morton [mailto:richard.e.morton@stripped]
Sent: Sunday, January 27, 2002 6:33 PM
To: bbean-list@stripped; DL Neil; mysql@stripped
Subject: RE: Selecting the row with largest number in a column


Thanks, but using

SELECT MAX(age) FROM contacts

only gives me the maximum age, not the person with that age.

The table is tiny, changes very often and therefore indexes would be a waste
of time.

Thanks

Richard

-----Original Message-----
From: Butch Bean [mailto:bbean-list@stripped]
Sent: 25 January 2002 20:04
To: DL Neil; Richard Morton; mysql@stripped
Subject: RE: Selecting the row with largest number in a column


The fastest way I found would be:

SELECT MAX(age) FROM contacts

good luck
bb

-----Original Message-----
From: DL Neil [mailto:PHPml@stripped]
Sent: Saturday, January 26, 2002 12:14 PM
To: Richard Morton; mysql@stripped
Subject: Re: Selecting the row with largest number in a column


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



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread97476@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-bbean-list=gedanken.net@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



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