How to use the ROW_NUMBER () sorting function in SQL

I have previously written an article to sort and mentioned the ROW_NUMBER () function. However, many people did not know the detailed usage of this function for the first time.

MK is posted here for your reference:

Three methods for Mssql to obtain 10th to 20th consecutive records

1. Use the row_number () function for numbering: as shown in figure

SELECT email, customerID, ROW_NUMBER () OVER (order by psd) as rows from QT_Customer
Principle: first sort by psd, and then number each piece of data.

2. Sort the order by Price in ascending order and sort each record

The code is as follows:

Select did, customerID, totalPrice, ROW_NUMBER () OVER (order by totalPrice) as rows from OP_Order
3. Calculate all the orders of each household, sort the orders of each customer in ascending order, and number the orders of each customer.
In this way, you will know how many orders each customer has.

As shown in the following figure:

The code is as follows:

The code is as follows:	Copy code
SELECT ROW_NUMBER () OVER (partition BY customerID order by totalPrice) as rows, customerID, totalPrice, did from OP_Order

4. Calculate the number of orders placed by each customer recently.

The code is as follows:

The code is as follows:	Copy code
WITH tabs ( SELECT ROW_NUMBER () OVER (partition BY customerID order by totalPrice) as rows, customerID, totalPrice, did from OP_Order )   Select max (ROWS) AS 'order times', customerID FROM tabs group by customerID

5. Count the minimum amount purchased in all orders of each customer, and count the number of times the customer purchased the order.

As shown in the preceding figure, rows indicates the number of times the customer made the purchase.

Idea: use a temporary table to perform this operation

1. Group by customer first, and then sort by customer's order time and number.

2. Then, use the subquery to find the minimum price for each customer's purchase.

3. Find the corresponding record based on the minimum price of each customer.

The code is as follows:

The code is as follows:	Copy code
WITH tabs ( SELECT ROW_NUMBER () OVER (partition BY customerID order by insDT) as rows, customerID, totalPrice, did from OP_Order ) SELECT * FROM tabs WHERE totalPrice IN ( Select min (totalPrice) FROM tabs group by customerID )

6. Filter the orders placed by the customer for the first time.

Row4
Train of Thought. Use rows = 1 to query the order record of the customer for the first time.

The code is as follows:

The code is as follows:	Copy code
WITH tabs ( SELECT ROW_NUMBER () OVER (partition BY customerID order by insDT) as rows, * FROM OP_Order ) SELECT * FROM tabs where rows = 1   SELECT * FROM OP_Order

7. rows_number () can be used for paging

Idea: first filter out all products and then number these products. Then, filter in the where clause.

8. Note: when using over and other window functions, the execution of grouping and sorting in over is later than that of "where, group by, order.

The following code:

The code is as follows:	Copy code
SELECT ROW_NUMBER () OVER (partition BY customerID order by insDT) as rows, CustomerID, totalPrice, DID FROM OP_Order WHERE insDT> '2017-07-22'

The above code executes the where clause first, and then numbers each record after execution.

In addition to the above, you can also use it for paging

For example, multiple paging methods of SQL statement ROW_NUMBER

Method 1

The code is as follows:	Copy code
Select top @ pageSize * from company where id not in (Select top @ pageSize * (@ pageIndex-1) id from company)

 
Method 2 ROW_NUMBER () OVER
 

The code is as follows:

Copy code

-- ROW_NUMBER () is to generate an ordered row number, and its standard for generating the ORDER is the OVER (order by id) followed) -- You must also add an OVER statement to tell SQL Server how to add row numbers. Select getdate () Select * from company where id in ( -- Search all the numbers in the settable table, that is, the IDs in the company Table. Here, we only need to get num (that is, sequential numbers) Select id from -- Search all IDs in the table, create a column of num to access the sorting number, and assign this table to settable (Select id, row_number () over (order by id) Num from company) As settable -- Add a search condition index and page size Where num between (@ pageIndex-1) * @ pageSize + 1 and @ pageIndex * @ pageSize) Select getdate ()