In SQL Sever 2008, if the implementation queries the first few of each course?

In SQL Sever 2008, if you need to query the top 3 of each course, the implementation is as follows:

Existing score table (result), column (Studentno, subjectno account number, Studentresult student score)

Method One:

SELECT distinct b.* Fromresult as R

Cross APPLY (

SELECT TOP (3) *from Result WHERE r.subjectno= subjectno ORDER by Studentresult DESC) as B

Order by Subjectno, Studentresult desc

Cross apply () use the APPLY operator to invoke a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function acts as the right input and the outer table expression as the left input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input

Method Two:

SELECT * FROM (

Select Rnk=dense_rank () over (partition by Subjectno ORDER by Studentresultdesc), *

From Result

) as T

where RNK <= 3--the top 3 in each class

Dense_rank () returns the rank of the row in the result set partition without any breaks in the rank, and if the values are the same, the sequence has a side-by-side condition. The row is ranked equal to the number of all positions before the row discussed plus one.

Over () in

<partition_by_clause>

Divides the result set generated by the FROM clause into several partitions where the Dense_rank function is applied

<order_by_clause>

Determines the order in which the Dense_rank value is applied to the rows in the partition. Integers cannot represent columns in the <order_by_clause> used in the ranking function.

Method Three:

SELECT * FROM (

Select Row_number () over (partition by Subjectno ORDER by Studentresultdesc) as C, * from result

) as T

where c<=3

row_number () returns the serial number of the expert in the result set partition, with the first row of each partition starting from 1

Over () , in the same way as method two.

The second way of writing has a side-by-side consideration, that is, if there are 2 grades in the subject and 3rd place, then this account can query out 4 records.

In SQL Sever 2008, if the implementation queries the first few of each course?