CODEWITHSUNDEEP
mysql
SHEETAL
SHEETAL

Reputation: 1

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

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 .

Upvotes: 0

Views: 61

Answers (1)

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 1

Related Questions