Use sqlserver to sequentially number each group of records.

Use sqlserver to sequentially number each group of records.

Use sqlserver to sequentially number each group of records

Project business needs to number each group of records to help you locate a specific row for each person when performing column conversion. After SQL Server 2005, The row_number () function is introduced, the grouping and sorting function of row_number () makes this operation very simple. Example:

Create a table and enter test data.

Create table test_001 (
User_id varchar (32 ),
Price decimal (10, 0)
)

Insert into test_001 values ('000000', 4000 );
Insert into test_001 values ('000000', 3900 );
Insert into test_001 values ('000000', 3800 );

Insert into test_001 values ('002 ', 4000 );
Insert into test_001 values ('002 ', 3800 );
Insert into test_001 values ('002 ', 3700 );

Run the following statement to rename each group of data;
Select ROW_NUMBER () over (PARTITION by user_id order by price desc) sort_num, USER_ID, price
From test_001
Order by USER_ID, price desc
The result is as follows: