TSQL: over clause

Question

Please help me undestand how order by influences to over clause. I have read msdn and one book and still misunderstood.

Let's say we have such query:

SELECT Count(OrderID) over(Partition By Year(OrderDate)),*
  FROM [Northwind].[dbo].[Orders]
  order by OrderDate

The result is that each raw has the column with the value how many entries in the table have the same year.

But what's happened when i try this query?:

SELECT ROW_NUMBER() over(Partition By Year(OrderDate)
 order by OrderDate) as RowN,*
  FROM [Northwind].[dbo].[Orders]
  order by RowN

Now I see the only thing that each RowN has 3 different years for each value (1996, 1997, 1998). I expected that RowN will be the same value for all 1996 year dates. Please explain me what happens and why.

Answer 1

The results will make far more sense if you do this:

SELECT
    Year(OrderDate),
    ROW_NUMBER() over(Partition By Year(OrderDate)order by OrderDate) as RowN,
    *
FROM [Northwind].[dbo].[Orders]
ORDER BY Year(OrderDate), RowN

Now you can see that each year has increasing row numbers starting from 1, ordered by order date:

Year RowN Order Date
1997 1    10400 1997-01-01 00:00:00
1997 2    10401 1997-01-01 00:00:00
1997 3    10402 1997-01-02 00:00:00
...
1998 1    10808 1998-01-01 00:00:00
1998 2    10809 1998-01-01 00:00:00
1998 3    10810 1998-01-01 00:00:00
...

Answer 2

In this case:

SELECT ROW_NUMBER() over(Partition By Year(OrderDate)
 order by OrderDate) as RowN,*
  FROM [Northwind].[dbo].[Orders]
  order by RowN

What you're seeing it it's giving you a row number that is partitioned by year, meaning that each year has it's own climbing row number. To make this a bit cleaerer in the results:

SELECT ROW_NUMBER() over(Partition By Year(OrderDate)
 order by OrderDate) as RowN,*
  FROM [Northwind].[dbo].[Orders]
  order by RowN, Year(OrderDate)

This means that each year, say 1997, will have orders 1 through n ordered by the date that year...like this was the 1st order of 1997, 2nd order of 1997, etc.