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

Gayathri Venugopal

DayEnd	Reorder	SalesDayInventory	Quantity	DayStart
39	0	39	25	64
64	54	10	65	75
75	65	10	8	18

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.

John Thorton

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.

Manik

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

Cheers,

Manik.

Peter Boekelaar

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

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

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

duplicate thread

Please don't post MYSQL issues in Oracle forum

Gaz in Oz

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.