SQL Server and Access data ranking (for example, score ranking)

However, before SQL server 2005, SQL SERVER 2000 did not provide this direct function for us to use. The same is true for ACCESS.

The following describes the implementation process of data ranking in two cases. The test data is as follows:

The result after ranking is as follows:

Access
Copy codeThe Code is as follows:
Select name, score, (select iif (isnull (sum (1), 1, sum (1) + 1) from score_rank where score>. score) as rank from score_rank a order by score desc

Sqlserver
Copy codeThe 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 a order by score desc

For SQL SERVER 2005 and later versions
Copy codeThe 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;