Sql query for Top N rows using group

Question

Hi, I tried many ways to solve this problem, but missed something. I have two tables Student and 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   As  1980    female
2   Al  1986    male
3   An  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 to return Sid and Smile to students who have the best 2nd grade for each course. If they exist, return the Sid and Sneim to the student who has the highest score among all male students for each course.

Here, the top 2 highest score is not only the top two entries in the group for ex: the top 2 is the highest score in the first group - 90, 80, 80.

I need to do this:

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 the following code:

select A.Sid , S.SNAME, Score,Cid
   from Score a,STUDENTS S
    where 2 >(select count(Cid)
               from Score
              where Cid=a.Cid
                and Score>a.Score)
    AND A.SID = S.SID
    order by Cid,Score desc

Answer 1

For the first question (list students who have a top 2 highest mark for each course), you should try the following:

SELECT SC.Sid , ST.SNAME, SC.Score, SC.Cid 
FROM (  SELECT *, DENSE_RANK() OVER(PARTITION BY Cid ORDER BY Score DESC) TopScore
        FROM Score) AS SC
INNER JOIN Students AS ST
ON SC.Sid = ST.Sid
WHERE SC.TopScore <= 2

(, ), :

SELECT A.Sid , A.SNAME, A.Score, A.Cid 
FROM (  SELECT SC.Sid , ST.SNAME, SC.Score, SC.Cid, DENSE_RANK() OVER(PARTITION BY Cid ORDER BY Score DESC) TopScore
        FROM Score AS SC
        INNER JOIN Students AS ST
        ON SC.Sid = ST.Sid
        WHERE ST.Ssex = 'male') A
WHERE A.TopScore = 1 

, .