Running Window functions in mysql 5.7

malikadeel

I am setting up following query in Mysql Server on my local pc and it was working fine Query is below: SELECT ID_KW,      KW,      INDEXED_DATE,      DAILY_VOLUME,      PREV_VOL,      DAILY_VOLUME - PREV_VOL DIFFERNCE  FROM (  SELECT B.ID_KW,                A.KW,                B.INDEXED_DATE,                B.DAILY_VOLUME,                LAG (DAILY_VOLUME, 1)                    OVER (PARTITION BY B.ID_KW ORDER BY B.INDEXED_DATE DESC)                    PREV_VOL            FROM KW_MASTER A, KW_VOLUME_EXACT B          WHERE A.ID_KW = B.ID_KW        ORDER BY A.KW, B.INDEXED_DATE DESC) kw When i run the same query in sqlfiddle it show me below error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY B.ID_KW ORDER BY B.INDEXED_DATE DESC) PREV_VOL' at line 12 Where do i need to change in above query so it will work in sqlfiddle also

Gaz in Oz

As stated in the SQL and PL/SQL thread:

mysql query having window function is not working in sqlfiddle

LAG() was introduced in 8.0 along with other window functions.

Did you use your favourite web search engine and find all the LAG() alternative ways?...

Does it really need to be 5.7 runnable?, if so why?

As stated in previous thread, sqlfiddle.com just hung, I could not access the site.

Please be much more specific, i.e include URL for the sqlfiddle site you are trying to run above code on, or state that you need the functionality akin to lag() as you are using a 5.7 db.

Again, as stated in your duplicate thread, db-fiddle.com defaults to 5.7, you change the default to 8.0 and lag() function wonks.

As I do not have your table/s or your data, here is an example with dummy data generated by an inline view, running in db-fiddle.com in mysql 5.5 db, and the 5.7 db:

select id,       action,       cast(prev_action as char)from (   select y.*,          @prev AS prev_action,          @prev := action   from  (            select 1 id, 'SELECT'  action from dual union all            select 5 id, 'UPDATE'  action from dual union all            select 2 id, 'INSERT'  action from dual union all            select 4 id, 'DELETE'  action from dual union all            select 3 id, 'UNKNOWN' action from dual   ) y, (select @prev:=NULL) varsorder by id) x 

Without the cast() function data in prev_action is of the form {"type":"Buffer","data":[83,69,76,69,67,84]}...

You would replace the in-line view lines 05. to 13. with you real table.

The above code was derived from an answer given in stackoverflow:

https://stackoverflow.com/questions/18437675/how-to-do-lag-operation-in-mysql

...found easily by using my favourite web search engine.

Gaz in Oz

As stated in the SQL and PL/SQL thread:

mysql query having window function is not working in sqlfiddle

LAG() was introduced in 8.0 along with other window functions.

Did you use your favourite web search engine and find all the LAG() alternative ways?...

Does it really need to be 5.7 runnable?, if so why?

As stated in previous thread, sqlfiddle.com just hung, I could not access the site.

Please be much more specific, i.e include URL for the sqlfiddle site you are trying to run above code on, or state that you need the functionality akin to lag() as you are using a 5.7 db.

Again, as stated in your duplicate thread, db-fiddle.com defaults to 5.7, you change the default to 8.0 and lag() function wonks.

As I do not have your table/s or your data, here is an example with dummy data generated by an inline view, running in db-fiddle.com in mysql 5.5 db, and the 5.7 db:

select id,       action,       cast(prev_action as char)from (   select y.*,          @prev AS prev_action,          @prev := action   from  (            select 1 id, 'SELECT'  action from dual union all            select 5 id, 'UPDATE'  action from dual union all            select 2 id, 'INSERT'  action from dual union all            select 4 id, 'DELETE'  action from dual union all            select 3 id, 'UNKNOWN' action from dual   ) y, (select @prev:=NULL) varsorder by id) x 

Without the cast() function data in prev_action is of the form {"type":"Buffer","data":[83,69,76,69,67,84]}...

You would replace the in-line view lines 05. to 13. with you real table.

The above code was derived from an answer given in stackoverflow:

https://stackoverflow.com/questions/18437675/how-to-do-lag-operation-in-mysql

...found easily by using my favourite web search engine.

malikadeel

Hi Gaz can you please use this Fiddle http://sqlfiddle.com/#!9/6fecaf/3 to Test My Query and Suggest how can i make same logic on MYSQL 5.7 Thank Adeel

Gaz in Oz

ok, so resorting to using a microsoft product "Edge"... it would appear that the sqlfiddle site loads, showing your create table scripts. Ugh. A non-compliant website that only works with certain browsers.

Anyway, on the right hand pane, doing a "select count(*) from kw_master;" returns 0 rows. Due to this and the fact you haven't shown what results you expect, it is impossible to write anything that will fit your unknown requirement, with any confidence.

Interestingly, sqlfiddle runs on 5.6 mysql. Any solution proffered would likely work on 5.7 but it begs the question why are you using sqlfiddle.com in the first place?