The instance explains the usage of the SQL server ranking function DENSE_RANK, And the instance explains dense_rank.

The instance explains the usage of the SQL server ranking function DENSE_RANK, And the instance explains dense_rank.

I. Requirements

Previously, the ranking function of SQL server used RoW_NUMBER () most. I usually use ROW_NUMBER () + CTE for paging. Today, I visited the park and saw another built-in ranking function, by the way, you can think about how to implement it in one minute.

The requirement is simple: Find the top five student information.

For example:

　　

Because the score can be tied together, the first five may have more than one. For example:

　　　　

Test data:

Declare @ t table (ID int, StudentName nvarchar (15), Score int) insert into @ tselect 1, 'Yellow 1', 99 union allselect 2, 'wu 2 ', 99 union allselect 3, 'zhang san', 99 union allselect 4, 'Li si', 98 union allselect 5, 'wang wu', 97 union allselect 6, 'zhao liu ', 96 union allselect 7, 'tian 7', 95 union allselect 8, 'ji 8', 94 union allselect 9, 'Qiu 9', 93 union allselect 10, 'lin 10', 92

Ii. Self-implemented

My idea: Since parallel expressions may occur, use DISTINCT to find the top five scores. 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 results are not the same as those described above. If there is less sorting, we can process the results in the program. This is not a problem.

3. Use the built-in ranking function DENSE_RANK

In fact, SQL server has built such a function to help us easily implement it. OK, go directly to the Code:

;with cte as(  select dense_rank() over(order by Score desc) rank,* from @t)select * from cte where rank < 6 　　

Iv. Extended, built-in ranking function RANK

Similar to DENSE_RANK, there is also a RANK function. However, RANK functions do not RANK sequentially, but are arranged according to the sequence number. If you change the above function to RANK (), the result is as follows:

　　

The above is how to use the SQL server ranking function DENSE_RANK. I have shared some of my ideas and hope to inspire everyone in learning.

Articles you may be interested in:

• Comparison of SQL2005 ranking functions (row_number, rank, dense_rank, and ntile)
• SQL Server 2005 ranking function usage Summary
• Usage of ROW_NUMBER, RANK, and DENSE_RANK in SQLSERVER 2005
• How to use oracle ranking Functions
• Detailed description of Oracle ranking function (Rank) Instances