getting a max value among groupS*

sfullman

Quick question,

select
MAX(field), field2, SUM(field3),
from
table
where CONDITION
group by field2

This obviously gives me the maximum value of the group field2. However, of all the groups that will be returned, there will also be a max value among SUM(field3).

I don't mind doing two separate queries, and in an UGLY way I can do this:

select
SUM(field3) as findMax
from
table
where CONDITION
group by field2,
order by findMax DESC

And of course in this case the first value is the max because I sorted DESCENDING.

It just seems there's a more elegant approach to say: "give me the max value of a sum function on a field, where the records are broken into such-and-such groups"

Thank you,
Sam Fullman

Lars_Berg

Using Mysql there is no alternative to your solution.

With SQL you can write


select max(cSum) from (
select
SUM(field3) as cSum
from
table
where CONDITION
group by field2 ) s

sfullman

Yep, thought it would boil down to a sub-select, thanks for helping solify that position. I look forward to the day when mySQL will do this (2004?) :-)

Lars_Berg

That is not a sub-select, it is a derived table.

tomhath

select
MAX(field), field2, SUM(field3),
from
table
where CONDITION
group by field2

This obviously gives me the maximum value of the group field2. However, of all the groups that will be returned, there will also be a max value among SUM(field3).

I don't mind doing two separate queries...

Since (presumably) you are fetching the rows in the result set from the query above, why not just keep track of the row that has the highest value for SUM(field3)?

Am I missing something here?

sfullman

tomhath: well actually, yes. I would like to know that information before I run the query in some separate query so I don't have to think about running the data into an array. Fundamental answer by Lars seems right that you cant get the max of a group with mySQL. I just figure that the more you use the database's native power vs. php scripting, usually the faster things'll run.

Best,
Sam

tomhath

Doesn't this get the max of the SUMs?

select
MAX(field), field2, SUM(field3),
from
table
where CONDITION
group by field2
order by 3 desc
limit 1