Known Participant

Question

get max of a query?

Forum|Forum|11 years ago
April 10, 2014
3 replies
1274 views

i want to get the max of this query.

<cfquery datasource="intranet" name="getMaxstars">
select submitterdept, sum((rating1+rating2+rating3+rating4+rating5)/5)/count(1) average_rating  
from CSEReduxResponses  
group by submitterdept  
order by 2 desc;  
</cfquery>
<cfset myarray=[submitterdept] >
<cfoutput  query="getMaxstars">
<h1>#ArrayMax(myarray)#</h1>
</cfoutput>

this code doesnt work. the query does, it will output 3 rows , so from this rows i want to get

the max of submitterdept, is there a different way to do this?

with the code abobe i get 'Variable SUBMITTERDEPT is undefined.'

This topic has been closed for replies.

BKBK

Community Expert

@no_name_123

I discovered when writing the last post that your original array idea was an inspired one after all! Here goes:

As in the case of a list, you could similarly find the next maximum as follows

maxItem: <cfoutput>#maxItem#</cfoutput><br>

max2Item: <cfoutput>#max2Item#</cfoutput><br>

max3Item: <cfoutput>#max3Item#</cfoutput>

Dave Ferguson

Participating Frequently

The output code is a bit odd. You are taking what appers to be a column from the query and putting that in an array. Then you are outputting the query and trying to output the max of the array. There are a couple issues with this.

First, the array is not being set to a valid var. You need to reference the query name and then a column not a column of the query. Second, outputting the query will cause it to loop over the query so you would get the same value over and over.

But to answer your question. Only loop if you need to. Looping over a query when you don't need to adds excess overhead that can be easily avoided. Here is an easy way to get the max without looping. This will create a list of all the average ratings, sort them and then get the largest value.

<cfoutput>#maxItem#</cfoutput>

HTH,

--Dave

BKBK

Community Expert

The problem line is : <cfset myarray=[submitterdept] >.

Submitterdept is unknown to ColdFusion!

Ideally, you should include the calculation of the maximum value in the SQL code. However, an alternative is as follows:

</cfif>

</cfoutput>

<cfoutput>#average_rating_max#</cfoutput>

N

no_name_123Author

Known Participant

thank you, now im able to get the max but how can i get the second max number (runner-up)

BKBK

Community Expert

no_name_123 wrote:
... how can i get the second max number (runner-up)

The valueList method Fergusondj suggests is, as he says, more efficient. In fact, I prefer it, too. It is, in addition, more versatile. You can adapt it to non-numerical values with just one switch of arguments in listSort.

To obtain what you want, you may use the fact that the maximum value is the last element of the sorted list, the second maximum the last but one on the list, that is, the element at position listLen(sortedList)-1, the third maximum the element at position listLen(sortedList)-2, and so on.

maxItem: <cfoutput>#maxItem#</cfoutput><br>

max2Item: <cfoutput>#max2Item#</cfoutput><br>

max3Item: <cfoutput>#max3Item#</cfoutput>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded