SQL Rank () function

RANK () over ([<partiton_by_clause>])


Partition_by_clause divides the result set generated by the FROM clause into the partition that is applied to the RANK function. &NBSP;
Order_by_clause determines the order in which the rank values are applied to rows in the partition.

The following is an example of a score Table
table schema
S_score (S_class (Class), s_id (school number), S_score (score))
SQL statement1:
Select rank () Over (order by s_score Desc) as position, S_class,s_id,s_score from S_score
result set as follows
Rank S_class s_id S_score
Class S200 12 90733
21 Class S20090635
33 class S20090919 S20090825
41 class S20090846 62
51 class S20090715
class 94
73 Class S20090836
82 class S20090631 Statement2:
Select rank () over (partition by S_class order B 　　Y s_score desc) as rank, s_class,s_id,s_score from S_score
result set as follows
Rank s_class s_id s_score
13 class S20090919 * Class 23 S20090836
12 class S20090733
22 class S20090715 94
32 class S20090631
11 class S20090635
21 class S20090846
31 class S20090825

The comparison ranking function for SQL2005 four ranking functions (Row_number, Rank, Dense_rank, and Ntile) is a new addition to SQL Server2005. There are four ranking functions in SQL Server2005:

1.row_number

2.rank

3.dense_rank

4.ntile

Dense_rank

The function of the Dense_rank function is similar to the rank function, except that it is sequential when the ordinal is generated, and the rank function may not produce sequential numbers. As in the above example, if you use the Dense_rank function, the 4th record should be numbered 2 instead of 4. As shown in the following SQL statement:

select dense_rank() over(order by field1),* from t_table order by field1

Explanation: http://www.cnblogs.com/xhyang110/archive/2009/10/27/1590448.html

SQL Rank () function