3

I get an execution error in following SQL script:

SELECT TOP 1 PERCENT
    a.accode, a.voucherdate, a.credit, a.Debit,
    SUM(a.Debit) OVER (ORDER BY [a.accode],[a.voucherdate]) AS rdr 
FROM
    VoucherMain AS a 
ORDER BY 
    a.accode, a.voucherdate

Error message

Incorrect syntax near 'order'

Can anyone tell me what's wrong with my syntax?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nilkanth Desai
  • 71
  • 2
  • 4

2 Answers2

6

The problem is that you need SQL Server 2012 and above. Okay, I added the "and above" for future visitors, but compare 2008 OVER CLAUSE with 2012 OVER CLAUSE.

The 2008 version has this important note:

When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
3

In SQL Server 2008, you can only use the OVER clause to partition aggregate functions, not apply an order:

Ranking Window Functions < OVER_CLAUSE > :: = OVER ( [ PARTITION BY value_expression , ... [ n ] ] < ORDER BY_Clause> )

Aggregate Window Functions < OVER_CLAUSE > :: = OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

Note that there's no <ORDER BY Clause> for the aggregates.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • If I have to get running total of debit in my above given query what options I have in SQL Server 2008 R2 with window function. Currently I am using CLR integrated function for running total. If this Window function is better performance over CLR based function then it will be great. IF any blog or link which can show sample execution of such window function with SQL Server 2008 R2 it will be helpful. Nilkanth – Nilkanth Desai Oct 02 '12 at 14:01
  • @NilkanthDesai - Running totals can be computed in SQL - generally by using either a recursive common table expression (CTE) or a "triangle join" (a join where the condition is an inequality, such as `<`). Searches on this site or elsewhere for running totals in SQL should find some decent answers. – Damien_The_Unbeliever Oct 02 '12 at 14:04
  • Perfect answer! I was using the ORDER BY clause and when I just moved the field to the PARTITION the query worked perfectly. `SUM(EE.IsRecScheduled) OVER(PARTITION BY EE.EmployeeId, EE.EVENTDATE) "IsDayEmplScheduled"` – SherlockSpreadsheets Dec 12 '18 at 22:01