Selecting the highest average !

[AntBon]

I am new to SQL, and i would like to know how i can select the highest average from a group selection.<br>
<br>
ie SELECT REGNO,AVG(CWK) FROM RESULT GROUP BY MODULE.<br>
<br>
This will bring back x averages grouped by module, how do i select the highest average from this, and then use the associated REGNO for another table<br>
<br>
I hope that makes sense, and i hope someone can help<br>
<br>
Ant

 

[stoleru]

It can't be possible to use group functions like that.<br>
You can use them in form like that:<br>
SELECT REGNO,AVG(CWK) FROM RESULT GROUP BY REGNO.<br>
<br>
In this case, the answer to your problem could be:<br>
select regno,avg<br>
from (select regno,avg(cwk) avg from result<br>
group by regno)<br>
where avg=(select max(avg) from (select regno,avg(cwk)<br>
avg from result group by regno));<br>
<br>
<br>
<p>Eduard Stoleru<br><a href=mailto:aeg@ziua.ro>aeg@ziua.ro</a><br><a href= > </a><br>