SQL Server ranking function: Dense_rank

First, the demand

Before the ranking function of SQL Server should be row_number (), I usually use the row_number () + CTE to achieve paging; today, the park, see another built-in ranking function is also good, by the way I think of a demand, You can take 1 minutes to figure out how to do it.

　　The demand is simple: ask for the top five student information.

For example:

　　

Because the scores can be tied, so the top five may have more than one. For example:

　　　　

Test data:

Declare @t table (ID int, studentname nvarchar (), score int) insert into @t Select 1, ' Yellow One ', Union allselect 2, ' Wu er ', Uni On Allselect 3, ' Zhang San ', Union allselect 4, ' John Doe ', 98 Union allselect 5, ' Harry ', Union allselect 6, ' Zhao Liu ', Union allselect 7, ' Tianqi ', Allselect Union 8, ' Ji Eight ', 94 Union Allselect 9, ' Qiu Jiu ', Allselect 10, ' Lin 10 ', 92

Second, the realization of their own

My idea: Since there may be juxtaposition, then use DISTINCT to find the top five of the results. OK, the code is as follows:

Select t1.* from @t t1join (select DISTINCT Top 5 score from @t ORDER by score desc) T2on T1. Score = t2. Score

It looks like the result of the above requirements is still not the same, the less the sort, of course we can process in the program, this is not the problem.

Third, using the built-in ranking function Dense_rank

　　In fact, SQL Server has built-in such functions can help us easily implement, OK, directly on the code:

; with CTE as (select Dense_rank () over (order BY score Desc) rank,* from @t) SELECT * from CTE where rank < 6

Iv. extension, built-in ranking function rank

Similar to Dense_rank there is a rank function, but the rank function does not rank in order, but is based on the ordinal row. A bit around, the above function to rank () to know, the results are as follows:

　　

SQL Server ranking function: Dense_rank