CODEWITHSUNDEEP
mysqlsql
synccm2012
synccm2012

Reputation: 497

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

Upvotes: 1

Views: 72

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can do it using a correlated sub-query:

SELECT col1, (SELECT col1 
              FROM mytable AS t2
              WHERE t2.col1 < t1.col1
              ORDER BY col1 DESC LIMIT 1) AS newcol
FROM mytable AS t1

Demo here

Note: The query works as long as col1 doesn't contain any duplicates.

Upvotes: 0

David Isla
David Isla

Reputation: 629

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This is a bit complicated, but the following handles it:

SELECT COL1,
       (CASE WHEN (@oldprev := @prev) = NULL THEN NULL -- NEVER HAPPENS
             WHEN (@prev := col1) = NULL THEN NULL     -- NEVER HAPPENS
             ELSE @oldprev
        END) as prevcol
FROM MYTABLE CROSS JOIN
     (SELECT @prev := 0) params
ORDER BY COL1;

As a note: oldprev doesn't need to be initialized because its value is used only in the case.

Upvotes: 2

Subin Chalil
Subin Chalil

Reputation: 3660

SELECT T1.`col1` AS col1,
(IFNULL(T2.`col2`,0)) AS new_col
FROM
table_name T1 LEFT JOIN table_name T2 ON T1.`col1`=(T2.`col1` - 1)

Upvotes: 0

Related Questions