oracle - SQL- Finding the users with the Maximun average score -

here schema:

create table sample (   userid      int,   score       int );  insert sample values (1,10); insert sample values (1,15); insert sample values (1,20);  insert sample values (2,100); insert sample values (2,200); insert sample values (2,500);  insert sample values (4,100); insert sample values (4,200); insert sample values (4,500);  insert sample values (3,5); insert sample values (3,5); insert sample values (3,10); insert sample values (3,7); insert sample values (3,2); 

i want find user id's of have maximum highest average score. note there more one! above sample data, answer be: 2 , 4, becuase both have average score of 266.666... .

i have working sql problem:

select s.userid sample s group userid having avg(s.score) in (   -- gets maximum average score (returns 1 result)   select max(average_score) max_average_score   (     -- gets average score     select avg(s2.score) average_score     sample s2     group userid   ) ); 

but think bit inefficient because i'm calculating average score twice. once main sql , again finding max avg score. there better way?

note: i'm using sql plus

many thanks!

i can how solve db2 sql create temponary table can save average score each user , select maximum value it. possible in sql plus too: how create temporary table in oracle.

here solution in db2 syntax (not tested)

http://www.cs.newpaltz.edu/~pletcha/db/db2_temptables.html

with temptable  (select userid, avg(score) sample group userid)  select * temptable score = (select max(score) temptable)