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 results, assuming the following:

Name	Subjects	Scores
John doe	Mathematical	100
John doe	Chinese	85
Tom	Mathematical	95
Tom	Chinese	90

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 (ORDER by [score] desc) as ID
From [table1] ORDER by [course]

The resulting query results are as follows:

Name	Subjects	Scores	Ranking
John doe	Mathematical	100	1
Tom	Mathematical	95	2
Tom	Chinese	90	3
John doe	Chinese	85	4

This is not the result we want, because math and language in the ranking is meaningless. To achieve the ranking within each account, Shenyang 463 plastic Surgery Hospital Program to our proposal is row_number () over () in the use of the PARTITION by keyword, PARTITION by is the role of a specified column to group the result set first, and then rank, For example, we need to group courses in this example, so that is partition by [course], all 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:

Name	Subjects	Scores	Ranking
John doe	Mathematical	100	1
Tom	Mathematical	95	2
Tom	Chinese	90	1
John doe	Chinese	85	2

This is the result we want.

Implementing grouping ranking in SQL Server