RE: Selecting the row with largest number in a column
>You are correct... my apologies, I was thinking in VB where I built a
handler to let me sent multiple sql statements in one chunk and where the
'@' doesn't matter.
BB
This Works as a test...
my $getbigage = $dbh->prepare(
"SELECT \@maxage:=max(age) from contacts");
my $getbigage1 = $dbh->prepare(
"SELECT * FROM contacts WHERE age=\@maxage");
$getbigage->execute();
$getbigage1->execute();
while(my @h = $getbigage->fetchrow_array()){
print "The max age is ==> $h[0]\n"; #see the answer
}
my @list = ();
my $target = undef;
while(my @h = $getbigage1->fetchrow_array()){
$fname = $h[0];
$lname = $h[1];
$age = $h[2];
print "The contact(s) I was looking for ==> $fname, $lname, $age\n";
push(@list, [$h[0],$h[1],$h[2]]);
}
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 28, 2002 11:28 AM
To: [EMAIL PROTECTED]; Richard Morton; DL Neil;
[EMAIL PROTECTED]
Subject: RE: Selecting the row with largest number in a column
>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.
-
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
RE: Selecting the row with largest number in a column
You are correct... my apologies, I was thinking in VB where I built a handler to let me sent multiple sql statements in one chunk and where the '@' doesn't matter. BB -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 11:28 AM To: [EMAIL PROTECTED]; Richard Morton; DL Neil; [EMAIL PROTECTED] Subject: RE: Selecting the row with largest number in a column 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 - 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
RE: Selecting the row with largest number in a column
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 - 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
RE: Selecting the row with largest number in a column
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
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
Re: Selecting the row with largest number in a column
Thanks for that BB. How large was the test table (rows and cols)? Did you test the alternative expressions by conducting a number of queries (to note the impact of any caching)? Did putting an index on age have any impact? (I would expect ORDER BY to improve) Please advise, =dn - Original Message - From: "Butch Bean" <[EMAIL PROTECTED]> To: "DL Neil" <[EMAIL PROTECTED]>; "Richard Morton" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: 25 January 2002 20:03 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 > > - 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
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
Re: Selecting the row with largest number in a column
At 17:13 + 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; - 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
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
Re: Selecting the row with largest number in a column
Richard Morton a écrit : > > Hello, > > 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.? > > Richard > hi, This request should work: SELECT * FROM contacts ORDER BY age DESC LIMIT 1; Hope this helps -- Joseph Bueno NetClub/Trader.com - 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
