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

    Question

  • 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

    Thursday, December 14, 2006 5:13 AM

Answers

  • 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.
    Thursday, December 14, 2006 7:40 AM

All replies

  • 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

    Thursday, December 14, 2006 6:09 AM
  • 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.
    Thursday, December 14, 2006 7:40 AM