Rank-Grouping by 2 columns

[gbhs]

Hi Experts
I don't have the least idea on how to rank (desc) the following
sample ExamScores by CourseID , grouping by Level.
Some help needed.

gbhs Newbie

Level   StudentID   CourseID    CourseName  ExamScore   ScoreRank
A       101         1           Math            11  
A       101         2           French          3   
A       101         3           English         7   
A       101         4           Biology         16  
A       102         1           Math            7   
A       102         2           French          13  
A       102         3           English         9   
A       102         4           Biology         10  
A       103         1           Math            12  
A       103         2           French          14  
A       103         3           English         2   
A       103         4           Biology         17  
B       104         1           Math            4   
B       104         2           French          12  
B       104         3           English         7   
B       104         4           Biology         1   
B       105         1           Math            3   
B       105         2           French          13  
B       105         3           English         4   
B       105         4           Biology         3   
B       106         1           Math            11  
B       106         2           French          14  
B       106         3           English         6   
B       106         4           Biology         2   

[gbhs]

Finally, I got it.

SELECT C.level,C.studentid,C.courseid,C.coursename,C.Examscore,

(select count(*) from mytable C1 where C.courseid = C1.courseid and  C1.level = C.level
and (C1.Examscore > C.Examscore or (C1.Examscore = C.Examscore and C1.level >= C.level ))) as Rnk

FROM mytable C

It gave me what I needed.
(inspired from Click Here)

Level   StudentID   CourseID    CourseName  ExamScore   Rnk
A       101         1           Math        11          2
A       101         2           French      3           3
A       101         3           English     7           2
A       101         4           Biology     16          2
A       102         1           Math        7           3
A       102         2           French      13          2
A       102         3           English     9           1
A       102         4           Biology     10          3
A       103         1           Math        12          1
A       103         2           French      14          1
A       103         3           English     2           3
A       103         4           Biology     17          1
B       104         1           Math        4           2
B       104         2           French      12          3
B       104         3           English     7           1
B       104         4           Biology     1           3
B       105         1           Math        3           3
B       105         2           French      13          2
B       105         3           English     4           3
B       105         4           Biology     3           1
B       106         1           Math        11          1
B       106         2           French      14          1
B       106         3           English     6           2
B       106         4           Biology     2           2

[eralper]

Hello gbhs,
SQL Server has introduced new features called Windowing functions like Row_Number, Rank, NTile, etc
Please refer to SQL tutorial titled Exam Scores Grouped by Their Classes Using DENSE_RANK() just on this topic

Please check the following SQL code which uses DENSE_RANK() function for sorting students according their exam scores categorized by more than one column

select 
    *, 
    DENSE_RANK() OVER (Partition By [Level], CourseID Order By ExamScore DESC)
from myTable