Forum Stats

  • 3,734,173 Users
  • 2,246,901 Discussions
  • 7,857,155 Comments

Discussions

SQL Query on setting up the previous value of the adjacent column to the other column

Gayathri Venugopal
Gayathri Venugopal Member Posts: 108
edited Mar 31, 2019 9:55AM in MySQL Community Space
DayEndReorderSalesDayInventoryQuantityDayStart
390392564
6454106575
756510818

Above is the output of the expected data. From the second row of DayEnd, I need to take the value of the previous row of the DayStart.How do I do that.If you observe the values of  DayEnd, it would have the data of the previous row value of the DayStart.

Tagged:
Gaz in OzFrank Kulash

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 27, 2019 10:38PM
    Gayathri Venugopal wrote:DayEndReorderSalesDayInventoryQuantityDayStart3903925646454106575756510818Above is the output of the expected data. From the second row of DayEnd, I need to take the value of the previous row of the DayStart.How do I do that.If you observe the values of DayEnd, it would have the data of the previous row value of the DayStart.

    Please click on URL below & provide details as stated in #5 - #9 inclusive

    How do I ask a question on the forums?

    We don't have your table.

    We don't have your data.

    We don't have your requirements.

    Gaz in OzFrank Kulash
  • Manik
    Manik Member Posts: 2,906 Gold Trophy
    edited Mar 28, 2019 2:10AM

    Question unclear... are you looking for LEAD LAG functions of oracle?

    Cheers,

    Manik.

  • Peter Boekelaar
    Peter Boekelaar Member Posts: 11
    edited Mar 28, 2019 4:45AM

    There...

    with test_data as
    ( select 0 reorder, 39 sales_day_inventory, 25 quantity, 64 day_start from dual union
      select 54,10,65,75 from dual union
      select 65,10,8,18 from dual)

    select lag(day_start) over (order by reorder) day_end
       , reorder
       , sales_day_inventory
       , quantity
       , day_start
      from test_data

  • Gayathri Venugopal
    Gayathri Venugopal Member Posts: 108
    edited Mar 28, 2019 5:28AM

    Hi i am using mysql server version 8 I am getting an error like this: Error Code: 1064. 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 '(order by ShippedDate) DayEnd    , reorder    , sales_day_inventory    , quantity' at line 1 0.000 sec

  • Peter Boekelaar
    Peter Boekelaar Member Posts: 11
    edited Mar 28, 2019 5:45AM

    Uhmmm... you posted this question without any context in a forum for Oracle. Did you expect the same syntax?

    Please post your question on a MySQL forum

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 29, 2019 10:15AM

    duplicate thread

    Please don't post MYSQL issues in Oracle forum

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Mar 31, 2019 9:55AM

    In Oracle database it would look like this, for example:

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/LAG.html

    with x as (   select  0 reorder, 39 sales_day_inventory, 25 quantity, 64 day_start from dual union all   select 54,         10,                     65,          75           from dual union all   select 65,         10,                      8,          18           from dual)select lag(day_start, 1, sales_day_inventory) over (order by reorder) day_end,       reorder,       sales_day_inventory,       quantity,       day_startfrom   x;   DAY_END    REORDER SALES_DAY_INVENTORY   QUANTITY  DAY_START---------- ---------- ------------------- ---------- ----------        39          0                  39         25         64        64         54                  10         65         75        75         65                  10          8         183 rows selected.SQL>

    In mysql 8 and above it would look like this:

    https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag

    with x as (   select  0 reorder, 39 sales_day_inventory, 25 quantity, 64 day_start from dual union all   select 54,         10,                     65,          75           from dual union all   select 65,         10,                      8,          18           from dual)select lag(day_start, 1, sales_day_inventory) over w day_end,       reorder,       sales_day_inventory,       quantity,       day_startfrom   xwindow w as (order by reorder);

    As usual it is all laid out in the respective documentation.

    LAG() function in mysql did not exist till version 8.

Sign In or Register to comment.