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
select*, sum(scenariokey)over(order by amount) as test1
and I get the following error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Thanks in Advance
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