How to achieve data ranking by Sql Server and Access

This article mainly introduces "how to achieve data ranking by Sql Server and Access". In daily operation, I believe many people have doubts about how to achieve data ranking in Sql Server and Access. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to achieve data ranking by Sql Server and Access". Next, please follow the editor to study!

However, before SQL SERVER 2005, SQL SERVER 2000 did not provide this direct function for us to use, and neither did ACCESS.

Below we divide into two situations, to write out the implementation process of data ranking. The test data are as follows:

The results after the ranking are as follows:

Access

The copy code is as follows:

Select name, score, (select iif (isnull (sum (1)), 1, sum (1) + 1) from score_rank where score > a.score) as rank from score_rank an order by score desc

Sqlserver

The copy code is as follows:

Select name, score, (select ISNULL (sum (1), 0) + 1 from score_rank where score > a.score) as rank from score_rank an order by score desc

For SQL SERVER 2005 and later

The copy code is as follows:

SELECT name, score, RANK () OVER (ORDER BY score DESC) AS [rank], DENSE_RANK () OVER (ORDER BY score DESC) AS [rank1], NTILE (4) OVER (ORDER BY score DESC) AS [rank2] FROM score_rank ORDER BY score DESC

At this point, the study on "how to achieve data ranking by Sql Server and Access" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!