Forum Stats

  • 3,732,983 Users
  • 2,246,665 Discussions
  • 7,856,453 Comments

Discussions

Running Window functions in mysql 5.7

malikadeel
malikadeel Member Posts: 86 Red Ribbon
edited January 2019 in MySQL Community Space

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

Dave Stokes-MySQL Community Team-Oracle

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited January 2019 Accepted Answer

    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.

    Dave Stokes-MySQL Community Team-Oracle

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited January 2019 Accepted Answer

    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.

    Dave Stokes-MySQL Community Team-Oracle
  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited January 2019

    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
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited January 2019

    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?

Sign In or Register to comment.