SQL Server Sort functions

The rank function is a new addition to the SQL Server2005 function. The following four rank functions are available in SQL Server2005:

1. Row_number

2. Rank

3. Dense_rank

4. Ntile

First, Row_number

The use of the Row_number function is very extensive, and the function of this function is to generate an ordinal number for each row of records that is out of the query. The use of the Row_number function is as shown in the following SQL statement:

Select Row_number () field1 as row_number,* from t_table

Where the Row_number column is the ordinal column that is generated by the Row_number function. You use the Row_number function to choose to sort a column by using the OVER clause before you can generate an ordinal number.

In fact, the rationale for the Row_number function to generate ordinal numbers is to use the sort statements in the over clause to sort the records, and then to generate the ordinal number in that order. The ORDER BY clause in the OVER clause does not have any relationship to a clause in the SQL statement, which can be completely different from each other, as shown in the following SQL statement:

Select Row_number () over (order by field2 Desc) as row_number,* fromt_table ORDER BY field1 Desc

We can use the Row_number function to implement a specified range of records in a query table, which is typically applied to the paging functionality of a Web application. The following SQL statement can query the 2nd and 3rd records in the T_table table:

The above SQL statement uses a CTE, an introduction to the CTE will refer to the SQL Server2005 Chat (1): simplifies nested SQL using common table expressions (CTE).

Also note that if you use the Row_number function for paging, the order by in the over clause and the ordered by of the sort record should be the same, otherwise the generated ordinal number may not be sequential.

Of course, not using the Row_number function can also implement the query specified range of records, is more cumbersome. The general approach is to use upside down to implement, for example, Query t_table table 2nd and 3rd records, you can first detect the first 3 records, and then the query out of the three records in reverse order, and then take the first 2 records, and finally will be found out of the 2 records in reverse order, is the final result. The SQL statement is as follows:

SELECT * FROM (SELECT top 2 * "(SELECT top 3 * t_table order Byfield1)" A "ORDER by field1 Desc" b Order by field1

This query results in addition to no ordinal column row_number

Second, rank

The rank function takes into account the same value of the sorted field in the over clause

The Field1 field values for the following three records in the records shown in Figure 6 are the same. If you use the Rank function to generate an ordinal, the ordinal number of these 3 records is the same, and the 4th record generates an ordinal number based on the current record count, followed by the record, and so on, that is, in this example, the 4th record is numbered 4 instead of 2. The rank function is used in exactly the same way as the Row_number function, and the SQL statement is as follows:

Select Rank () over (the order by field1), * from T_table to field1

Third, 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 ordinal number generated by the rank function may be discontinuous. As in the example above, 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 to field1

Four, Ntile

The Ntile function can group ordinal processing. This is equivalent to putting the query's recordset into an array of specified lengths, each containing a certain number of records. The ordinal number generated by the Ntile function for each record is the index of all the array elements of this record (starting from 1). You can also call the array element of each assigned record "bucket." The Ntile function has an argument to specify the number of buckets. The following SQL statement uses the Ntile function to drum the t_table table:

Select Ntile (4) over (order by field1) as bucket,* from t_table

Since the total number of records in the T_table table is 6, the Ntile function in the above SQL statement specifies a bucket of 4.

Perhaps some readers will ask such a question, how does SQL Server2005 decide how many records a bucket should put in? Maybe there are few records in the T_table table, so let's assume that there are 59 records in the T_table table, and the number of buckets is 5, so how many records should be put in each bucket?

In fact, with two conventions you can produce an algorithm to determine which bucket should put how many records, these two conventions are as follows:

1. The number of small bucket records can not be smaller than the number of barrels. In other words, the number of records in the 1th poke can only be greater than the record in the 2nd barrel and in subsequent buckets.

2. All the records in the bucket are either the same, or the number of records in the bucket after the start of a bucket with a smaller record is the same as the number of records in the barrel. In other words, if there is a bucket, the first three barrels of records are 10, and the 4th Poke record number is 6, then the 5th barrel and the 6th barrel record number must also be 6.

According to the above algorithm, if the total number of records is 59, the number of barrels is 5, then the first 4 barrels of records are 12, the last bucket record number is 11.

If the total number of records is 53 and the number of barrels is 5, the first 3 barrels have a record number of 11, and the last 2 barrels are 10.

Take this example, the total number of records is 6, the number of barrels is 4, then calculate the value of RecordCount1 is 2, after the end while loop will calculate the value of RecordCount2 is 1, therefore, the first 2 barrels of records 2, 2 barrels after the record is 1.