Examples explain the usage of SQL Server ranking function Dense_rank _mssql

I. Demand

The top function of SQL Server in the first place is row_number (), I usually use row_number () + CTE to achieve paging; today I go to the garden, I see another built-in ranking function is not bad, I think of a need, You can spend 1 minutes trying to figure out how to do it.

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

For example:

　　

Because the results can be tied, so the top five may have multiple. For example:

　　　　

Test data:

Declare @t table
(ID int,
 studentname nvarchar (),
 Score int)
 
INSERT INTO @t
Select 1, ' Yellow One ', 99 UNION ALL
Select 2, ' Wu er ', the union ALL
Select 3, ' John ', the union ALL
Select 4, ' Dick ', the Union all
Select 5, ' Harry '  , the union ALL
Select 6, ' Zhao Liu ', the union ALL
Select 7, ' Tianqi ', the union ALL
Select 8, ' Ji VIII ', the UNION ALL
Select 9, ' Qiu JIU ', union ALL
Select 10, ' Lin 10 ', 92

Second, to achieve their own

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

Select t1.* from @t T1
join (SELECT DISTINCT Top 5 Score to @t ORDER by Score Desc) in
T2 on. Score = t2. Score

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

Third, use the built-in ranking function Dense_rank

In fact, SQL Server has built such a function to help us easily implement, OK, directly on the code:

, with CTE as (
  select Dense_rank () Score desc) rank,* from @t
)
select * from CTE where rank < 6

Iv. expansion, built-in rank function rank

Like Dense_rank There is also a rank function, but the rank function is not ranked sequentially, but is sorted by ordinal. A bit around, change the above function to rank () to know, the results are as follows:

　　

The above is the SQL Server ranking function Dense_rank use method, share some of their ideas, hope to learn some inspiration.