problems with sum()over(order by field) syntax

Hello

I'm experimenting with window functions and I'm having troubles with the sum()over() syntax.

I've followed the syntax from many examples and i'm still getting this error. I can get it work with sum(field1) over (partition by field2) but every time i add order by i get an error

I have the following query, this is from the adventureworks sample

use adventureworksdw

select *, sum(scenariokey)over(order by amount) as test1

from FactFinance

and I get the following error

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'order'.

Any suggestions.

Thanks in Advance

Daniel

When you use aggregation you have to use Partition By instead of Order BY.

select *, sum(scenariokey)over(Partition By amount) as test1

When you use Ranking functions then you have to use Order By & you can use Partition By if nessasary..

select *, Rank() over (Partition By OrderDate Order By amount) as test1

select *, Row_Number() over (Partition By OrderDate Order By amount) as test1

|||SQL Server 2005 only supports partition clause for aggregate window functions. The ranking window functions support partition and order by. So we implement only a small subset of the OVER clause as specified in the ANSI SQL standards.