However, before SQL Server 2005, SQL Server 2000 did not provide this direct function for us to use, as did ACCESS.
Below we divide 2 kinds of circumstances, to write the data ranking realization process. The test data are as follows:
The results of the rankings are as follows:
Access
Copy Code code as follows:
Select name, Score, (select IIF (SUM (1), 1, sum (1) + 1) from Score_rank where score > A.score) as rank from Scor E_rank a ORDER BY score Desc
Sql server
Copy Code code as follows:
Select name, score, (select ISNULL (SUM (1), 0) + 1 from Score_rank where score > A.score) as rank to score_rank a order By score Desc
For SQL SERVER 2005 and later
Copy Code code 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 the Score_rank order by score DESC;
