Usage of row_number in SQL Server

Returns the serial number of an expert in the result Set Partition. The first row of each partition starts from 1.

Transact-SQL syntax conventions

Syntax

Row_number( )Over([<Partition_by_clause>] <order_by_clause>)

Parameters

< Partition_by_clause>

Partition the result set generated by the from clause into the partition where the row_number function is applied. For more information about partition by syntax, see over clause (TRANSACT-SQL ).

< Order_by_clause>

Determine the order in which the row_number value is allocated to the rows in the partition. For more information, see the order by clause (TRANSACT-SQL ). When <order_by_clause> is used in the ranking function, an integer cannot be used to represent a column.

Return type

Bigint

Remarks

The order by clause determines the order in which a unique row_number is allocated for rows in a specific partition.

Example A. Return the sales staff's line number

The following example will be returned based on sales since the beginning of the yearAdventureworksMedium sales personnel'sRow_number.

	Copy code
Select firstname, lastname, row_number () over (order by salesytd DESC) as 'row number', salesytd, postalcode from sales. vsalespersonwhere territoryname is not null and salesytd <> 0;

B. Return the subset of rows.

The following example returns the row number50To60And pressOrderdateSort.

	Copy code
Use adventureworks; gowith orderedorders as (select salesorderid, orderdate, row_number () over (order by orderdate) as 'rownumber' from sales. salesorderheader) Select * From orderedorders where rownumber between 50 and 60;

C. Use row_number () with partition

The following example showsRow_numberFunctions andPartitionParameters are used in combination.

	Copy code
Select firstname, lastname, row_number () over (partition by postalcode order by salesytd DESC) as 'row number', salesytd, postalcode from sales. vsalespersonwhere territoryname is not null and salesytd <> 0;

 

Update learning:

Http://topic.csdn.net/u/20111227/21/a978539d-6d3b-4751-9588-193f9fe6fe80.html? 1801