title: SQL - how to get previous value and nexrt vale in rows in mysql?
body: <pre><code>create table office(rec_id varchar(10),curr_off varchar(10),orderflag int);
  
  
  insert into office values
  ('A1','A11',1),
  ('A1','A12',2),
  ('A1','A13',3),
  ('A1','A14',4),
  ('B1','B11',1),
  ('B1','B12',2),
  ('B1','B13',3),
  ('B1','B14',4),
  ('C1','C11',1),
  ('C1','C12',2),
  ('C1','C13',3),
  ('C1','C14',4);
  </code></pre>
  
  <p>union query</p>
  
  <pre><code>SELECT REC_ID,CURR_OFF,ORDERFLAG
  FROM OFFICE a1 where rec_id = ‘A1’
  union
  SELECT REC_ID,CURR_OFF,ORDERFLAG
  FROM OFFICE a1 where rec_id = ‘B1’
  Union
  SELECT REC_ID,CURR_OFF,ORDERFLAG
  FROM OFFICE a1 where rec_id = ‘C1’
  ORDER BY REC_ID,ORDERFLAG DESC;
  </code></pre>
  
  <p>expected result </p>
  
  <pre><code>Rec_id  Curr_off    Order_flag  Pre_off Next_off
    A1      A14            4         A13     null
    A1      A13            3         A12     A14
    A1      A12            2         A11     A13
    A1      A11            1         null    A12
    B1      B14            4         B13     null
    B2      B13            3         B12     B14
    B3      B12            2         B11     B13
    B4      B11            1         null    B12
    C1      C14            4         C13     null
    C2      C13            3         C12     C14
    C3      C12            2         C11     C13
    C4      C11            1         null    C12   
  </code></pre>
  
  <p>how can i get the result set like this, this is dummy data where i have to apply logic for more union quieris and i can not extend unions and reduce also. </p>