p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Selecting the record with the greatest number


Message #1 by "John Arbon" <subscriptions@c...> on Mon, 2 Sep 2002 16:12:36
How would you write a mySQL query to select only the record with the 
greatest (largest) number of a specific field?

I have looked through the mySQL manual and found GREATEST(), but it 
appears to require arguments to choose from.  All I want is the one record 
with the largest number in the specified field, and that number will be 
unknown.

Thanks! 

John
Message #2 by "jorge" <jorge@d...> on Mon, 2 Sep 2002 10:33:06 -0500
hi there

this is like a cheat
order the query
ORDER BY myfield DESC LIMIT 1

Jorge
----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Monday, September 02, 2002 4:12 PM
Subject: [beginning_php] Selecting the record with the greatest number


> How would you write a mySQL query to select only the record with the
> greatest (largest) number of a specific field?
>
> I have looked through the mySQL manual and found GREATEST(), but it
> appears to require arguments to choose from.  All I want is the one record
> with the largest number in the specified field, and that number will be
> unknown.
>
> Thanks!
>
> John


Message #3 by "jorge" <jorge@d...> on Mon, 2 Sep 2002 10:35:48 -0500
BUT  you can always use MAX in your query
SELECT MAX(mytable.myfield);
This is no cheat

Jorge :)
----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Monday, September 02, 2002 4:12 PM
Subject: [beginning_php] Selecting the record with the greatest number


> How would you write a mySQL query to select only the record with the
> greatest (largest) number of a specific field?
>
> I have looked through the mySQL manual and found GREATEST(), but it
> appears to require arguments to choose from.  All I want is the one record
> with the largest number in the specified field, and that number will be
> unknown.
>
> Thanks!
>
> John


Message #4 by "Pedro" <ufhbiqwj001@s...> on Mon, 2 Sep 2002 17:20:01 +0200
select max(field) from table

> -----Original Message-----
> 
> How would you write a mySQL query to select only the record with the 
> greatest (largest) number of a specific field?
> 
> I have looked through the mySQL manual and found GREATEST(), but it 
> appears to require arguments to choose from.  All I want is 
> the one record 
> with the largest number in the specified field, and that 
> number will be 
> unknown.

Message #5 by "John Arbon" <subscriptions@c...> on Mon, 2 Sep 2002 16:57:44
Thanks for such quick replies!

John
Message #6 by "John Arbon" <subscriptions@c...> on Mon, 2 Sep 2002 17:03:08
One more question:

Would the query "SELECT MAX(myTable.field2 + myTable.field2);"

add together the fields from the SAME record and then pick the one with 
the greatest total?

John
Message #7 by "Pedro" <ufhbiqwj001@s...> on Mon, 2 Sep 2002 18:07:01 +0200
yes :)

> -----Original Message-----
> 
> One more question:
> 
> Would the query "SELECT MAX(myTable.field2 + myTable.field2);"
> 
> add together the fields from the SAME record and then pick 
> the one with the greatest total?

if myTable has

id  field1  field2  ...
 1      10      20
 2      11      21
 3      11      20
...

your SELECT MAX(myTable.field2 + myTable.field2);
would return a row with "32"

Message #8 by "John Arbon" <subscriptions@c...> on Mon, 2 Sep 2002 18:00:03
Thank you!  Thank you! 8)

John
Message #9 by "John Arbon" <subscriptions@c...> on Wed, 4 Sep 2002 02:45:28
Ok, to expand on that idea, I tried this:

SELECT * FROM contest WHERE MAX(refCount + revCount);

and got the error:

Invalid use of group function
SELECT * FROM contest WHERE MAX(refCount + revCount) 

Can you tell me how a query would be written to select all the fields of a 
record that has the greatest sum of two of it's fields out of all the 
other records?  And if there are tow or more records of the same summed 
value, would it select them as well?

John
Message #10 by "jorge" <jorge@d...> on Tue, 3 Sep 2002 21:07:45 -0500
Hey John please rephrase your question?

Jorge
:)
----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Wednesday, September 04, 2002 2:45 AM
Subject: [beginning_php] RE: Selecting the record with the greatest nu mber


> Ok, to expand on that idea, I tried this:
>
> SELECT * FROM contest WHERE MAX(refCount + revCount);
>
> and got the error:
>
> Invalid use of group function
> SELECT * FROM contest WHERE MAX(refCount + revCount)
>
> Can you tell me how a query would be written to select all the fields of a
> record that has the greatest sum of two of it's fields out of all the
> other records?  And if there are tow or more records of the same summed
> value, would it select them as well?
>
> John


Message #11 by "John Arbon" <subscriptions@c...> on Wed, 4 Sep 2002 03:08:47
LOL!  Ok, let me try this.

I have a table that has 4 fields.  Of those 4 fields there are 2 that are 
of type INT. (call them num1 and num2)  What I am trying to do is retrieve 
ALL the fields of the record that has the greatest sum of the two numeric 
fields (sum = num1 + num2).

I COULD add a field to the table that holds the sum of num1 and num2, but 
I don't think I need to do that.

So, what I am trying to do instead is figure out how to write a query that 
adds these two fields together, and then based upon the sum retrieves the 
record with the largest sum.

Does that clear it up any? 8)

John
Message #12 by "jorge" <jorge@d...> on Tue, 3 Sep 2002 21:59:38 -0500
Hi john
first
how do you plan to solve the case when two fields add to the same value?

JOrge

----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Wednesday, September 04, 2002 3:08 AM
Subject: [beginning_php] RE: Selecting the record with the greatest nu mber


> LOL!  Ok, let me try this.
>
> I have a table that has 4 fields.  Of those 4 fields there are 2 that are
> of type INT. (call them num1 and num2)  What I am trying to do is retrieve
> ALL the fields of the record that has the greatest sum of the two numeric
> fields (sum = num1 + num2).
>
> I COULD add a field to the table that holds the sum of num1 and num2, but
> I don't think I need to do that.
>
> So, what I am trying to do instead is figure out how to write a query that
> adds these two fields together, and then based upon the sum retrieves the
> record with the largest sum.
>
> Does that clear it up any? 8)
>
> John


Message #13 by "jorge" <jorge@d...> on Tue, 3 Sep 2002 22:26:06 -0500
John
 now the query

SELECT MAX(num1 + num2) FROM mytable

have fun

Jorge
:)
I cant think of a way to make a direct sql query since the sql functions
they can sum count, average,max, min,
----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Wednesday, September 04, 2002 3:08 AM
Subject: [beginning_php] RE: Selecting the record with the greatest nu mber


> LOL!  Ok, let me try this.
>
> I have a table that has 4 fields.  Of those 4 fields there are 2 that are
> of type INT. (call them num1 and num2)  What I am trying to do is retrieve
> ALL the fields of the record that has the greatest sum of the two numeric
> fields (sum = num1 + num2).
>
> I COULD add a field to the table that holds the sum of num1 and num2, but
> I don't think I need to do that.
>
> So, what I am trying to do instead is figure out how to write a query that
> adds these two fields together, and then based upon the sum retrieves the
> record with the largest sum.
>
> Does that clear it up any? 8)
>
> John


Message #14 by "jorge" <jorge@d...> on Tue, 3 Sep 2002 22:58:34 -0500
John
now i got what you want

here is the qquery you need  you have 4 fields
name,last,num1,num2

SELECT name,last,MAX(num1 +num2) FROM `aprendiendo` GROUP BY name LIMIT 1

Jorge

:)
----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Wednesday, September 04, 2002 3:08 AM
Subject: [beginning_php] RE: Selecting the record with the greatest nu mber


> LOL!  Ok, let me try this.
>
> I have a table that has 4 fields.  Of those 4 fields there are 2 that are
> of type INT. (call them num1 and num2)  What I am trying to do is retrieve
> ALL the fields of the record that has the greatest sum of the two numeric
> fields (sum = num1 + num2).
>
> I COULD add a field to the table that holds the sum of num1 and num2, but
> I don't think I need to do that.
>
> So, what I am trying to do instead is figure out how to write a query that
> adds these two fields together, and then based upon the sum retrieves the
> record with the largest sum.
>
> Does that clear it up any? 8)
>
> John


Message #15 by CathyV227@a... on Tue, 3 Sep 2002 23:49:22 EDT
????????????????????
Message #16 by "John Arbon" <subscriptions@c...> on Wed, 4 Sep 2002 05:37:42
Jorge,

Gracias!  But it does not work.  I ran this query:

SELECT contribName, revCount, refCount,
       MAX(refCount + revCount)
       FROM contest
       GROUP BY contribName
       LIMIT 1;

with records of:
refCount  revCount
   3         2
   2         3
   120       6

And it still listed the first record as the one with the greatest sum...
*sigh*

I was hoping to avoid the use of yet another field, or SELECTING each and 
every record and then using a PHP function to ferret out the winning 
record.  But, I have been searching the mySQL manual for a solution and 
have not yet found it.

If there are any other suggestions, I'm all eyes!

Thanks.

John
Message #17 by "jorge" <jorge@d...> on Wed, 4 Sep 2002 07:04:56 -0500
HEY John The query I sent you WORKS PERFECTLY
you rewrote it wrongly.
Please look carefully at my query you cant call revCount,refCount
individually since you are alredy calling them in the MAX () function.

just do as i told you before

SELECT contribName,MAX(refCount + revCount) FROM contest GROUP BY
contribName LIMIT 1

:)
Jorge Cordero
de nada
Greetings


----- Original Message -----
From: "John Arbon" <subscriptions@c...>
To: "beginning php" <beginning_php@p...>
Sent: Wednesday, September 04, 2002 5:37 AM
Subject: [beginning_php] RE: Selecting the record with the greatest nu mber


> Jorge,
>
> Gracias!  But it does not work.  I ran this query:
>
> SELECT contribName, revCount, refCount,
>        MAX(refCount + revCount)
>        FROM contest
>        GROUP BY contribName
>        LIMIT 1;
>
> with records of:
> refCount  revCount
>    3         2
>    2         3
>    120       6
>
> And it still listed the first record as the one with the greatest sum...
> *sigh*
>
> I was hoping to avoid the use of yet another field, or SELECTING each and
> every record and then using a PHP function to ferret out the winning
> record.  But, I have been searching the mySQL manual for a solution and
> have not yet found it.
>
> If there are any other suggestions, I'm all eyes!
>
> Thanks.
>
> John


Message #18 by "Nikolai Devereaux" <yomama@u...> on Wed, 4 Sep 2002 11:45:51 -0700
Okay, the difference between Jorge's solution and your needs is that he's
_NOT_ returning the original values of refCount and revCount in the SELECT
clause, and you need them.

This is one of the many places where mysql's lack of nested-query support
would be ideal:

SELECT C1.contribName, C1.revCount, C1.refCount
  FROM contest C1
 WHERE (C1.revCount + C1.refCount) 
         (SELECT MAX(C2.revCount + C2.refCount)
            FROM contest C2
         )


To do this with mySQL, you might need to just pull things apart:

$query  = "SELECT MAX(revCount + refCount) FROM contest";
$result = mysql_query($query);
$max    = mysql_result($result, 0);

$query = "SELECT contribName, revCount, refCount
            FROM contest
           WHERE (revCount + refCount) = $max";
$result = mysql_query($query);
// you know the rest...



Take care,

Nik


  Return to Index