Implementing grouping rankings in SQL Server

For example, we have such a table, the table name is table1, the table is stored in a class of students all the subjects of the test scores,

Assume the following:

At this time, the teacher asked to find out the scores of students in each subject, ranking function before we used the most is row_number () over (). However, by default, this ranking function is not a branch of the purpose, that is, not grouped, it will be all the results according to high or low ranking

Select * , Row_number ()over (ORDERby[ score ]desc  As from[table1]ORDERby [  course ]

The resulting query results are as follows:

This is not the result we want, because math and language are ranked together without any meaning. To achieve the ranking within each account, we are going to use the Partition by keyword in row_number ()  over (), the function of Partition by is to group the result set according to a specified column, then rank, For example, we need to group courses in this example, so that is the partition by [course], all of the SQL is as follows:

 select  * , Row_number () over  (PARTITION by  [   course  ]   ORDER  by  [  score  ]  desc ) as   ID  from   [ table1  ]  order  by  [  course  ]  

The resulting query results are as follows:

This is what we're looking for.

Implementing grouping rankings in SQL Server