CODEWITHSUNDEEP
mysqlsql
Fofole
Fofole

Reputation: 3568

select data from previous row as current row data

I have the following select in my mysql database:

select t.col1, t.THE_DATE, (select ?? as PREVIOUS_DATE) 
from DUMMY_TABLE t 
order by date

What I am trying to achieve is have the 'PREVIOUS_DATE' contain the value of the previous row's 'THE_DATE' column if there is one.

So if DUMMY_TABLE has the data :

col1 THE_DATE 
x     10-01-2010
x     10-01-2012
x     10-01-2009

my select should return 

col1  THE_DATE   PREVIOUS_DATE
x    10-01-2009       
x    10-01-2010   10-01-2009
x    10-01-2012   10-01-2010

Upvotes: 0

Views: 50

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need order by clause in subquery with limit clause :

select t.col1, t.the_date, 
      ( select t1.the_date 
        from dummy_table t1
        where t1.col = t.col and
              t1.the_date < t.the_date
        order by t1.the_date desc
        limit 1
      ) as PREVIOUS_DATE 
from dummy_table t 
order by the_date;

Upvotes: 3

Related Questions