SQL Help Needed

[part_time_ch]

I have a table named test and the columns are sal and name.

i have tha following values

name sal
---- -----
A 1
B 9
A 9
B 5
A 8


i need the 'name' which gives the max(avg(sal)). it means name 'A' gives the avg of 6 and name 'B' gives the avg of 7.

now i need the name 'B' to be displayed.

i have given the query
'select avg(sal) sal from comp group by name'

but it returns both the value 6 and 7.

i need only the max sal.

plz help me.

 

[din]

Hmm

You need it in a single query without using any programming lang ? I mean Pure SQL ?

 

[part_time_ch]

i need it in a pure sql single query.

 

[ruturaj3]

I think u want o/p as 7

here is the query,
SELECT AVG(T1.SAL) AS "MAX SAL" FROM TEST T1 , TEST T2
GROUP BY T1.NAME
HAVING AVG(T1.SAL) > AVG(T2.SAL)