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:[EMAIL PROTECTED]]
Sent: Sunday, January 27, 2002 6:33 PM
To: [EMAIL PROTECTED]; DL Neil; [EMAIL PROTECTED]
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:[EMAIL PROTECTED]]
Sent: 25 January 2002 20:04
To: DL Neil; Richard Morton; [EMAIL PROTECTED]
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:[EMAIL PROTECTED]]
Sent: Saturday, January 26, 2002 12:14 PM
To: Richard Morton; [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
