Max(Avg(sql)) ??

Trying to figure out how to do a max on an average field. Can this be done at the PHP level or do I need server side stuff first.

Help much appreciated.

<?php
$RecordsetCreative = new WA_MySQLi_RS("RecordsetCreative",$xxxxx,1);
$RecordsetCreative->setQuery("SELECT Avg(tblStrainRatings.Rating) AS AvgRating, tblStrain.StrainName, tblQuestions.* FROM tblStrainRatings INNER JOIN tblStrain ON tblStrainRatings.StrainID = tblStrain.StrainID INNER JOIN tblQuestions ON tblStrainRatings.QuestionID = tblQuestions.QuestionID WHERE tblQuestions.QuestionCategoryID = 2 ORDER BY Avg(tblStrainRatings.Rating) DESC");
$RecordsetCreative->execute();
?>



SELECT Avg(tblStrainRatings.Rating) AS AvgRating, tblStrain.StrainName, tblQuestions.*
FROM tblStrainRatings INNER JOIN tblStrain ON tblStrainRatings.StrainID = tblStrain.StrainID INNER JOIN tblQuestions ON tblStrainRatings.QuestionID = tblQuestions.QuestionID
WHERE tblQuestions.QuestionCategoryID = 2
ORDER BY Avg(tblStrainRatings.Rating) DESC LIMIT 1

You would probably need a nested SELECT statement.

What exactly are you trying to do? You want to only show the highest average rating strain? Or are you ordering by the avg?

I think you might just want:

SELECT Avg(tblStrainRatings.Rating) AS AvgRating, tblStrain.StrainName, tblQuestions.*
FROM tblStrainRatings INNER JOIN tblStrain ON tblStrainRatings.StrainID = tblStrain.StrainID INNER JOIN tblQuestions ON tblStrainRatings.QuestionID = tblQuestions.QuestionID
WHERE tblQuestions.QuestionCategoryID = 2
GROUP BY tblStrain.StrainID
ORDER BY Avg(tblStrainRatings.Rating) DESC LIMIT 1