CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Wacao
Wacao

Reputation: 51

SQL Server "order by" and "rows" clause in window functions is not working in SQL Server 2014

I am getting error as:

Incorrect syntax near 'ROWS'.

While executing below query in SQL server 2014:

select  SC_CODE, [Date] , High ,Low, 
                                Max(High) over (partition by SC_CODE 
                                order by [Date]         
                                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as 7DayHigh,
                                High / Max(High) over (partition by SC_CODE) * 100 as Percentage_wrt_High,
                                Min(Low) over (partition by SC_CODE 
                                order by [Date]         
                                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as 7DayLow,
                                Low / Min(Low) over (partition by SC_CODE) * 100 as Percentage_wrt_Low
from dbo.NSE

To the best of my understanding, Windows articles, and this forum posts, partition by is supported in sql server 2012+.

screenshot of my Sql server 2014 Help --> About page

Kindly suggest if any plugin or update required to be installed in order to properly use partition by clause. Or any workaround that may be used.

Additional Findings: If I comment out the rows between part, then i am getting error as:

Incorrect syntax near 'order'.

And if I comment out the order by part also, the query gets executed without any error.

So in a nutshell the partition by clause is working fine, but the order by and rows sub-clause when added to the query, I get the error mentioned above.

This is a very basic query for now, but going forward, I intend to add a lot more columns for analysis purpose and determining details like, past 7 days high, past 1 month high, past 52 weeks high, 7 days moving average, 1 month moving average, etc

Any suggestions/workarounds that may be extended to my larger scope are also welcomed. Thanks.

Upvotes: 1

Views: 775

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

select 1 as 8jh--this fails

select 1 as j8h--this works

select 1 as [8jh]--this works

If ask is regarding how to get rid of error..using brackets [7DayHigh] helped me parse the query..so query will get past syntax errors..

select  SC_CODE, [Date] , High ,Low, 
    Max(High) over (partition by SC_CODE      order by [Date]   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as [7DayHigh],
    High / Max(High) over (partition by SC_CODE) * 100 as Percentage_wrt_High,
    Min(Low) over (partition by SC_CODE   order by [Date]   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as [7DayLow],
    Low / Min(Low) over (partition by SC_CODE) * 100 as Percentage_wrt_Low
from dbo.NSE

Upvotes: 1

Related Questions