Rank by SQL

In many cases, we need statistics on the ranking, the most common is the score, the number of votes, and so on. However, before SQL Server 2005, SQL Server 2000 did not provide this direct function for us to use. The same is true for access.

Access:

 
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
 

Web SQL 2000:

 
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
 

Web SQL 2005:

 
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;
 

Use of select sum (1)
Select count (*) returns the number of all records that meet the condition, which is the same as select sum (1)
However, sum () can be used to transmit any number, either a negative number or a floating point number. The returned value is the input value n * Number of records meeting the conditions. m is simply understood as the number of rows * 1