get max of a query?

[no_name_123]

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.'

[BKBK]

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:

<cfset average_rating_max = 0>

<cfoutput query="getMaxstars">

    <cfif average_rating GTE average_rating_max>

        <cfset average_rating_max = average_rating>

    </cfif>

</cfoutput>

<cfoutput>#average_rating_max#</cfoutput>

[no_name_123]

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

[BKBK]

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.

<cfset list = valueList(getMaxstars.average_rating)>

<cfset sortedList = listSort(list, "numeric")>

<cfset listLength = listLen(sortedList)>

<cfset maxItem = listLast(sortedList)>

<cfset max2Item = listGetAt(sortedList,listLength-1)>

<cfset max3Item = listGetAt(sortedList,listLength-2)>

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

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

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

[Dave Ferguson]

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.

<cfset maxItem = listLast(listSort(ValueList(getMaxstars.average_rating), "numeric"))>

<cfoutput>#maxItem#</cfoutput>

HTH,

--Dave

[BKBK]

@no_name_123

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

<cfset list = valueList(getMaxstars.average_rating)>

<cfset array = listToArray(list)>

<cfset maxItem = arrayMax(array)>

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

<!--- A boolean! It says array is now sorted. --->

<cfset isSorted = arraySort(array,"numeric")>

<cfset arrayLength = arrayLen(array)>

<cfset max2Item = array[arrayLength-1]>

<cfset max3Item = array[arrayLength-2]>

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

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

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