oracle - Sql query for Top N rows using a group by -

hi tried many ways solve missing thing.. have 2 tables student , score

sid cid score 6   1   90 1   1   80 4   1   80 3   1   70 2   1   60 6   2   80 1   2   70 2   2   60 4   2   60 5   2   50 4   3   80 7   3   80 6   3   70 8   3   60 2   3   50  sid sname sbday   ssex 1    1980    female 2   al  1986    male 3    1989    male 4   ja  1986    male 5   ma  1983    female 6   phi 1986    male 7   geo 1993    male 8   lil 1990    female 9   cha 1993    male 

i need return sid , sname of students have top 2 highest score each course. if existed, return sid , sname of student has highest score among male students each course.

here top 2 highest score not top 2 records in group ex : top 2 highest score in 1st group 90, 80 ,80 .

i need out put

sid cid score 6    1   90 1    1   80 4    1   80 6    2   80 1    2   70 2    2   60 4    2   60 4    3   80 7    3   80 6    3   70 

i tried following code :

select a.sid , s.sname, score,cid    score a,students s     2 >(select count(cid)                score               cid=a.cid                 , score>a.score)     , a.sid = s.sid     order cid,score desc 

for first question (list students have top 2 highest score each course), should try this:

select sc.sid , st.sname, sc.score, sc.cid  (  select *, dense_rank() over(partition cid order score desc) topscore         score) sc inner join students st on sc.sid = st.sid sc.topscore <= 2 

for second quetion (the student has highest score among male students each course), following:

select a.sid , a.sname, a.score, a.cid  (  select sc.sid , st.sname, sc.score, sc.cid, dense_rank() over(partition cid order score desc) topscore         score sc         inner join students st         on sc.sid = st.sid         st.ssex = 'male') a.topscore = 1  

hope helps.