SQL using ROW_NUMBER() OVER PARTITION BY x ORDER BY y when x and y are the same for multiple rows-CodePudding

This is the code I am running:

ROW_NUMBER() OVER (PARTITION BY id ORDER BY a,b) as seq

This is an example of a table I am working with:

|  id |  a  | b  |name|
| --- | --- | -  | -- |
|  1  |12345| 14 |John|
|  1  |12345| 14 |Anne|
|  1  |23456| 14 |Dave|
|  2  |45445| 16 |Matt|

When a seq value is assigned to the first two rows, how is the order decided? Asking this as id, a and b are the same for both rows and it seems to change between different runs.

CodePudding user response：

how is the order decided?

The order will be whatever is most convenient for Sql Server. Sometimes this will be table order (as determined by the primary key (clustered index) of the source table rather than insert order), but lots of things can mess with this, such that you might even get different orders from one moment to the next even for the same query. If this matters, you must add more fields to the ORDER BY clause until it is specific enough.