To return a set of records in a certain order from Microsoft SQL Server 2005

Absrtact: The application of SqlServer2005 has been developed for some time, but many times it is SqlServer2005 as a SqlServer2000, so many of the new features of SqlServer2005 I did not use, One reason is to be compatible with SqlServer2000 users. The new features will certainly be used in the real world, and to know the new features of SQLServer2005 can be found in the Microsoft website's what ' s New in SQL Server 2005? , there are many features this article mainly describes the recordset that returns a particular order in the database.

Database model:

As we dive into the SQL sort statement, let's take a look at the database model, which is that we're going to build a database and use it for our explanation. We build a product database, first we want to create a product table products, including ProductID (primary key), Name,price three fields. This is a very simple database, we use this database to explain the following sort.

SQLSERVER2005 offers us four sort functions: Row_number, RANK, Dense_rank and Ntile, (as far as I know) these four functions are not available in SqlServer2000, and we can use these four functions on different occasions, They each have their own characteristics, let's take a look at each of their characteristics.

Row_number ():

This function is sorted by a specific column, which means we want to specify which column to sort by. We need to know his characteristics. First of all, we need to know his grammar:

Row_number () over ([Partition]order-by-clause)

Take a look at the example, for example, we want to from product products in the price of each product ranked out, and get the serial number of each product, the code is as follows:

SELECT[ProductID],[Name],[Price],
   
ROW_NUMBER()OVER(ORDERBY[Price]DESC)ASRank
FROMProducts

Then the rank column will appear in the returned result, and the column is going to line up 1 (1,2,3,...) from 1, so that we can use this feature very flexibly to page out the records. Let's take a look at how to achieve fast paging, assuming that we display a number of records per page @pagesize, the current page is @pageindex, we can use the following code to pagination:

  SELECTTOP(@PageSize)[ProductID],[Name],[Price]
 
FROM
 
(
SELECT[ProductID],[Name],[Price],ROW_NUMBER()OVER(ORDERBY[Price]DESC)ASRank
FROMProducts
 
)ASp
 
WHERERankBETWEEN((@PageIndex-1)*@PageSize+1)AND@PageSize*@PageIndex
  RANK(), DENSE_RANK():

From the example above, we know that the Row_number () function returns the number of rows arranged by a certain column. However, in two identical records, such as our products A and b prices are 1RMB, in Row_number () will return a B in the order of #, #2. But sometimes we need the same record to return the same line number, as mentioned above the return line number (a) #1 (b) #1, so we need to use here to describe the two functions RANK () and Dense_rank () function. Usage is the same as Row_number's usage:

Select[productid],[name],[price],

RANK () over (ORDERBY[PRICE]DESC) Asrank

Fromproducts

We can know that rank () and dense_rank () can achieve the effect, but the difference is that rank () will skip the line number of the same number of records. That is, although the line number is the same, the line number of the next page with a different column value will be the sum of these line numbers plus their line numbers. If the above nail B is #, the result of returning C with RANK () is #3, and the result of Dense_rank () returned by C is a.

Ntile ():

We use ntile when we need to sort the groups (), for example, we divide all products from big to small in terms of price, and we can use the following code:

Selectproductid,name,price,ntile (5) over (ORDERBYPRICEDESC) ASP

Fromproduts

This divides all records into 5 parts, divided by 1 to 5 at a price level.

Summarize:

The four functions described in this article, Row_number, rank, Dense_rank and ntile, are able to sort the return recordset, and the most I want to use is to apply row_number for quick paging. In fact, their four each have their own characteristics, in our peacetime application of flexible applications they can bring us a lot of convenience, a lot of happiness! At the same time also hope that this article can bring you some help, bring a little happiness!