mysqlsql

Getting previous column values in a new column

I have the following table, named Example:

 col1   
 ----------
 101   
 102   
 103   
 104

I want below result existing column with one addition result

col1   newcol
---------------
 101   0/''/null
 102   101
 103   102
 104   103


 SELECT COL1, @a := @a AS col2
 FROM MYTABLE JOIN (SELECT @a := 0) t
 ORDER BY COL1;

Can I get the previous column value in the second column corresponding to the first column

Solution

  • You can use a to save the last one.

    SELECT @a as Prev, @a:=COL1 as Current
FROM MYTABLE
ORDER BY COL1;

    You can find further information in MySQL User-Defined Variables

    I hope that you will find it useful.

    EDITED:

    Removed the initializing sentence of @a:

    SET @a=0;

    First value of @a will be NULL