Trying to understand over() and partition by

Try ordering by order date, you'll see the results more easily

select Row_Number() over(partition by orderdate order by orderdate asc)     as Rownumber, salesorderid, orderdatefrom test2order by orderdate;

should give (i've added blank lines for clarity)

ROWNUMBER     SALESORDERID       ORDERDATE1             43664              07/01/20011             43911              08/01/20012             43867              08/01/20013             43895              08/01/20014             43894              08/01/20015             43877              08/01/20011             44109              09/01/20011             44285              10/01/20011             44483              11/01/20012             44501              11/01/2001

You'll notice that the result is divided into 'partitions', each partition being the set of rows with identical orderdates. That is what 'partition by orderdate' means.

Within a partition, the rows are ordered by orderdate, as per the second clause of '(partition by orderdate order by orderdate asc)'. That isn't very useful, as all rows within a partition are going to have the same orderdate. Because of that, the ordering of the rows within a partition is random. Try ordering by salesorderid within the partition by clause to have a more reproducable result.

row_number() just returns the row's ordering within each partition

The partition by orderdate means that you're only comparing records to other records with the same orderdate. For example, of the five records with orderdate = '08/01/2001', one will have row_number() = 1, one will have row_number() = 2, and so on.

The order by orderdate asc means that, within a partition, row-numbers are to be assigned in order of orderdate. In your example that has no effect, because you're already partitioning by orderdate, so all records within a partition will have the same orderdate. (It would be like writing SELECT ... FROM t WHERE c = 6 ORDER BY c: all selected records have the same value of c, so the ORDER BY c does nothing.) So, within a partition, the assignment of row_number() is arbitrary: each row will have a different number, but there are no guarantees about which row will have which number.