SqlServerRow_Number () Learning

Row_Number (): row_number () is used to generate a row sequence number for each selected record in a certain order. Syntax: ROW_NUMBER () OVER ([PARTITIONBYvalue_expression,... [n] order_by_clause) The following is a Test example of learning row_number (): CREATETABLE # Test (TypeNa

Row_Number (): row_number () is used to generate a row sequence number for each selected record in a certain order. Syntax: ROW_NUMBER () OVER ([partition by value_expression,... [n] order_by_clause) The following is a Test example of learning row_number (): create table # Test (TypeNa

Row_Number ():

Row_number () is used to generate a row sequence number for each selected record in a certain order.

Syntax:

ROW_NUMBER () OVER ([partition by value_expression,... [n] order_by_clause)

The following is a test example of learning row_number:

Create table # Test
(
TypeName VARCHAR (50 ),
TestName VARCHAR (50 ),
UpdateDate DATETIME
)

Insert into # Test VALUES ('type1', 'test1', '2017-07-07 ')
Insert into # Test VALUES ('type1', 'test1', '2017-07-06 ')
Insert into # Test VALUES ('type1', 'test1', '2017-07-05 ')
Insert into # Test VALUES ('type2', 'test1', '2017-07-04 ')
Insert into # Test VALUES ('type2', 'test1', '2017-07-03 ')
Insert into # Test VALUES ('type2', 'test1', '2017-07-02 ')
Insert into # Test VALUES ('type2', 'test1', '2017-07-01 ')

1. Return by time in ascending order # The row number of the Test table:

SELECT ROW_NUMBER () OVER (order by UpdateDate) RowNumber ,*
FROM # Test

Result set:

1Type2Test12013-07-01 00:00:00. 000
2Type2Test12013-07-02 00:00:00. 000
3Type2Test12013-07-03 00:00:00. 000
4Type2Test12013-07-04 00:00:00. 000
5Type1Test12013-07-05 00:00:00. 000
6Type1Test12013-07-06 00:00:00. 000
7Type1Test12013-07-07 00:00:00. 000

2. sort by time using TypeName as the group:

SELECT ROW_NUMBER () OVER (partition by TypeName order by UpdateDate) RowNumber ,*
FROM # Test

Result set:

1 Type1 Test1 2013-07-05 00:00:00. 000
2Type1Test12013-07-06 00:00:00. 000
3Type1Test12013-07-07 00:00:00. 000
1Type2Test12013-07-01 00:00:00. 000
2Type2Test12013-07-02 00:00:00. 000
3Type2Test12013-07-03 00:00:00. 000
4Type2Test12013-07-04 00:00:00. 000

3. Find the data from the third to sixth in chronological order:

; WITH TestOrder
(
SELECT ROW_NUMBER () OVER (order by UpdateDate) RowNumber, * FROM # Test
)
SELECT * FROM TestOrder WHERE RowNumber BETWEEN 3 AND 6

Result set:

3 Type2 Test1 2013-07-03 00:00:00. 000
4Type2Test12013-07-04 00:00:00. 000
5Type1Test12013-07-05 00:00:00. 000
6Type1Test12013-07-06 00:00:00. 000

MSDN learning address

, U.S. space, Hong Kong virtual host, website Space