Search code examples
mysqlcreate-table

Creating new column with rows shifted up in MySql

I have following table T1 : 

ID | time
10 | 1000
10 | 1002
10 | 1003
11 | 1002
11 | 1004

Now I want to create a table T2 from T1 in which for each ID, successive time-intervals are shown i.e. 

ID | time1 | time 2
10 | 1000 | 1002
10 | 1002 | 1003
10 | 1003 | NULL
11 | 1002 | 1004
11 | 1004 | NULL

so time2 entry is basically the entry of time1 in next row.
How can I do it in MySql ?

Solution

  • Have a try with this one:

    SELECT
ID,
`time` AS time1,
(SELECT MIN(st.`time`) FROM T1 st WHERE st.ID = T1.ID AND st.`time` > T1.`time`) AS time2
FROM T1