Use SQL Server to number each group of records sequentially

Use SQL Server to number each group of records sequentially

Project business needs, the number of each group of records, to facilitate row to column, the person is positioned to a specific row; After SQL Server 2005, the Row_number () function is introduced, and the Row_number () function's grouping ordering feature makes this operation very simple. Examples are as follows:

First create the table and enter the test data.

CREATE TABLE test_001 (
user_id varchar (32),
Price Decimal (10,0)
)

INSERT into test_001 values (' 001 ', 4000);
INSERT into test_001 values (' 001 ', 3900);
INSERT into test_001 values (' 001 ', 3800);

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

Execute the following statement to renumber 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 results are as follows:

Use SQL Server to number each group of records sequentially