RANKING Functions in SQL SERVER Part II || ROW_NUMBER function in SQL SERVER 2014
This video will provide you the details of each RANKING Function in SQL Server 2014.
RANKING function PART I
• RANKING Functions in SQL SERVER Part ...
*****************************************************************************8
ROW_NUMBER (Transact-SQL)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
Arguments
PARTITION BY value_expression
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group
order_by_clause
The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required.
Return Types
Bigint
General Remarks
There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.
1. Values of the partitioned column are unique.
2. Values of the ORDER BY columns are unique.
3. Combinations of values of the partition column and ORDER BY columns are unique.
ROW_NUMBER() is nondeterministic.
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. For example, the function AVG always returns the same result given the qualifications stated above, but the GETDATE function, which returns the current datetime value, always returns a different result.