TSQL: above the sentence

Question

Please help me figure out how the order affects the over clause. I read msdn and one book and still do not understand.

Say we have a query like this:

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

As a result, each raw has a column with a value whose number of records in the table is equal to the same year.

alt text http://img-fotki.yandex.ru/get/3912/svin80.2/0_3b871_3bb591da_XL

But what happened when I tried this request ?:

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

alt text http://img-fotki.yandex.ru/get/3908/svin80.2/0_3b872_c9352fb1_XL

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

Answer 1

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 see, this gives you a line number that is divided by year, which means that each year has its own line number for climbing . To do this a little 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 every year, say, in 1997, it will have orders from 1 to n, ordered by the date of that year ... as it was the first order of 1997, the second order of 1997, etc.

Answer 2

The results will make 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 the number of rows is increasing every year, starting from 1, sorted 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 ...