How to use partition by and order by in over function?

Question

I'm using SQL Server 2008 R2.

I'm trying to write a query showing the following:

select productname, unitprice,categoryid, sum(unitprice) 
over (partition by categoryid order by unitprice desc) As PriceSum
from Products

I want the outcome to order products by their unit price, while partitioning the products by their categories. I'm getting this error: Incorrect syntax near 'order'. What am I doing wrong?

Solution

You are nothing doing wrong. SQL Server 2008 does not support running aggregates with windowing function.

SQL Server 2012 finally has full support for windowing functions, including running aggregations. So if you really need this, you will need to upgrade.

See this SQLFiddle for SQL Server 2012: http://sqlfiddle.com/#!6/5303f/1