1

I am executing the following query;

CREATE TABLE #temp_c 
(
     row_id NUMERIC(18) IDENTITY (1,1), 
     value NUMERIC(18)
)

INSERT #temp_c (value)
VALUES (1), (7), (10), (1), (12), (8), (6), (3)

SELECT 
    row_id,
    value,
    SUM(value) OVER (ORDER BY row_id) total 
FROM 
    #temp_c

DROP TABLE #temp_c

And expect the following results

row_id                                  value                                   total
--------------------------------------- --------------------------------------- ---------------------------------------
1                                       1                                       1
2                                       7                                       8
3                                       10                                      18
4                                       1                                       19
5                                       12                                      31
6                                       8                                       39
7                                       6                                       45
8                                       3                                       48

When executing this code on SQL Server 2012 the above query functions as expected.

When executing this code on SQL Server 2008 R2 the query returns the following error

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'order'.

According to this documentation I should be able to start using this query in 2008.

Please note the PARTITION BY syntax works on both versions, was the ORDER BY logic implemented at a later date?

James Luxton
  • 394
  • 2
  • 11
  • 2
    Short answer: Yes. The partition by is fine in 2008, the order by is 2012 onward. Unfortunately, this sort of thing needed to be achieved with something like a self-join or a cross apply (with the same kind of logic as a self-join) in 2008. – ZLK Oct 29 '18 at 21:43
  • 1
    Note the parts about aggregates in [this link](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189461(v=sql.105)) for reference. (Specifically: " cannot be used with aggregate window functions.") – ZLK Oct 29 '18 at 21:49
  • Thanks ZLK, hopefully I get something clean working here for both environments. – James Luxton Oct 29 '18 at 21:53
  • 1
    See this https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance – DancingFool Oct 30 '18 at 02:55

0 Answers0