What is the usage of the sql server ranking function DENSE_RANK

This article will explain in detail what the usage of the sql server ranking function DENSE_RANK is. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. demand

Before the sql server ranking function should be the most frequently used RoW_NUMBER (), I usually use ROW_NUMBER () + CTE to achieve paging; today, visit the park, see another built-in ranking function is not bad, by the way, I thought of a demand, you can take 1 minute to think about how to achieve.

The need is simple: ask for information about the top five students.

For example:

As the scores can be tied, there may be more than one of the top five. For example:

Test data:

Declare @ t table (ID int, StudentName nvarchar (15), Score int) insert into @ tselect 1, 99 union allselect 2, 99 union allselect 3 Zhang San, 99 union allselect 4 Li Si, 98 union allselect 5, 97 union allselect 6, 96 union allselect 7, 95 union allselect 8, 94 union allselect 9, Qiu Jiu, 93 union allselect 10, 92

Second, realize it by yourself

My idea: since there may be juxtaposition, then use DISTINCT to find the top five grades. 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 seems that the result is not the same as the above requirements, less sorting, of course, we can deal with in the program, this is not a problem.

Third, use the built-in ranking function DENSE_RANK

In fact, sql server has built-in such a function to 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

Fourth, expansion, built-in ranking function RANK

Similar to DENSE_RANK, there is a RANK function, but the RANK function is not ranked in order, but by ordinal number. It's a little roundabout. Just change the above function to RANK ().

About the use of sql server ranking function DENSE_RANK is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.