Shulou(Shulou.com)06/01 Report--

This article is about how to use the ROW_NUMBER function in SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

ROW_NUMBER

The use of row_number is very wide, sorting had better use him, generally can be used to achieve web program paging, he will query out of each row of records to generate a sequence number, sorted in turn and will not repeat, note that when using the row_number function must use the over clause to select a column to sort in order to generate the sequence number. Examples of row_number usage:

Select ROW_NUMBER () OVER (order by [SubTime] desc) as row_num,* from [Order]

The query results are shown in the following figure:

The row_num column in the figure is the sequence number column generated by the row_number function. The basic principle is to sort the records using the sort statement in the over clause, and then generate the sequence numbers in this order. The order by clause in the over clause has nothing to do with the order by clause in the SQL statement, and the order by can be completely different, for example, the following sql,over clause is sorted according to SubTime descending order, and the Sql statement is sorted in TotalPrice descending order.

Select ROW_NUMBER () OVER (order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc

The query results are shown in the following figure:

We can use row_number to realize the paging of web program, and we can query the table data of the specified range. Ex.: get the third to fifth pieces of data in reverse order according to the time of order submission.

With orderSection as (select ROW_NUMBER () OVER (order by [SubTime] desc) rownum,* from [Order]) select * from [orderSection] where rownum between 3 and 5 order by [SubTime] desc

The query results are shown in the following figure:

Note: when using row_number for paging, you should pay special attention to the fact that the order by in the over clause should be consistent with the order by in the Sql sorted record, otherwise the resulting sequence number may not be contiguous. Let's write an example to prove this by changing the sort field in the above Sql statement from SubTime to TotalPrice. Also mention that for queries with subqueries and CTE, the ordering of subqueries and CTE queries does not mean that the entire query is ordered unless order by is specified.

With orderSection as (select ROW_NUMBER () OVER (order by [SubTime] desc) rownum,* from [Order]) select * from [orderSection] where rownum between 3 and 5 order by [TotalPrice] desc

The query results are shown in the following figure:

Thank you for reading! This is the end of the article on "how to use the ROW_NUMBER function in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.