Mysql – Retrieve column info from next column

MySQL

I have a query that is working but I figure I could enlist some help to make it more efficient. However before I show the query I would like to get a few answer on how others would do this.

What the query is supposed to do is to select data from a table, however some of the data is calculated within the SELECT statement. One of those calculations is the retreival of data from the next row. For example, I select a date from the current record, but I also want to know the next row's date as well.

Sample data:

=======================
date        |   comment
=======================
2013-01-01  |   comment
2013-03-03  |   comment

Desired output:

    
======================================
date        |   leadDate   |   comment
======================================
2013-01-01  |   2013-03-03 |   comment
2013-03-03  |     null     |   comment

Right now I am using a stored function and a cursor to get this info and I tried using a LEFT JOIN which messed up my variables I am using.

I just wanted to get input from others on how they would do this.

Best Answer

The idea of next or previous rows makes sense only in the presence of explicitly specified ORDER BY clause.

Now assuming that you need your resultset ordered by date you can achieve your goal either by using a subquery

SELECT date,
      (
        SELECT date
          FROM table1
         WHERE date > t.date
         ORDER BY date
         LIMIT 1
      ) leadDate,
       comment
  FROM table1 t;

or by leveraging user(session) variables

SELECT date, leadDate, comment
  FROM
(
  SELECT date, CAST(@d AS DATE) leadDate, @d := date, comment
    FROM table1 CROSS JOIN (SELECT @d := NULL) i
   ORDER BY date DESC
) q
 ORDER BY date;

Sample output (in both cases):

+------------+------------+----------+
| date       | leadDate   | comment  |
+------------+------------+----------+
| 2013-01-01 | 2013-03-03 | comment1 |
| 2013-03-03 | NULL       | comment2 |
+------------+------------+----------+

Here is SQLFiddle demo

Related Question