[RESOLVED] Use AVG and ORDER BY DESC LIMIT 1 together?

[timska77]

Sorry for the weird title but I didn't know how else to title this. Is it possible to use a call like this so that I can get the average of all of the highest scores only from a DB table where any user may have more than one score for each test?

$sql = "SELECT AVG(score) FROM testscores WHERE testid='2' ORDER BY score DESC LIMIT 1";

Thanks in advance.

[Lars_Berg]

select avg(score)
  from 
         (select max(score) as score
             from testscores
            where testid = 2
            group by username) dt

Change column names as apropriate

[timska77]

Thanks Lars. I wasn't able to check for replies earlier. I'll give this a shot in a few minutes. Thanks again.

[timska77]

Thanks again. I had to do a join and that took me a while with this but I got it and your help is much appreciated.