Fetch next value/row of a column having multiple rows in mysql

Question

My table is like this::

+-------+-----------------+---+
 id     |  created             |
+-------+-----------------+---+
| 2sdv  | 2016-05-18 14:08:14 |
| 2sdv  | 2016-05-18 14:25:22 |
| 2sdv  | 2016-05-18 14:26:01 |
| 2sdv  | 2016-05-19 07:19:13 |
+-------+---------------------+

Lets say this id ='2sdv' created on these dates I want the ouput to be like this

+-------+-----------------+---+------+-----------------+
 id     |  created            | new_date
+-------+-----------------+---+------+-----------------+
| 2sdv  | 2016-05-18 14:08:14 | 2016-05-18 14:25:22    |
| 2sdv  | 2016-05-18 14:25:22 | 2016-05-18 14:26:01    |
| 2sdv  | 2016-05-18 14:26:01 | 2016-05-19 07:19:13    |
| 2sdv  | 2016-05-19 07:19:13 |
+-------+---------------------+------+-----------------+

In Oracle we can use lead() function but I want my query to be in MySQL. how can I get next value of created date column in a new column in multiple rows fetch .

You want the minimum y date greater than the corresponding x date. Because you also want the latest x date, this would be a LEFT JOIN — Strawberry
– Strawberry, Commented Jun 3, 2016 at 6:58
In this particular case my id is same but i want to add a new column having next value of date created — SHEETAL
– SHEETAL, Commented Jun 3, 2016 at 7:03
This is not something MySQL is designed to be able to do. You should solve this problem outside MySQL. It's easily solved in a language like PHP for example — Jester
– Jester, Commented Jun 3, 2016 at 7:18

Strawberry · Accepted Answer · 2016-06-03 09:22:09Z

1

As per my comment above...

SELECT x.*
     , MIN(y.created) new_date -- the minimum y date...
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id = x.id 
   AND y.created > x.created -- ... greater than the corresponding x date
 GROUP 
    BY x.id
     , x.created;

answered Jun 3, 2016 at 9:22

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetch next value/row of a column having multiple rows in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related