Getting values from previous rows using user-defined variable in MySQL

Question

I have a table named as "t1", my table is-

+------+------+
| c1   |  c2  |
+------+------+
|  1   |   12 |
|  2   |   13 |
|  3   |   14 |
+------+------+

I want to get all rows along with the previous values of col2. The query I use:-

Select c1,@prev,@prev:=c2 from t1;

I get following output-

+------+---------+------------+
| c1   |  @prev  | @prev:=c2  |
+------+---------+------------+
|  1   |   NULL  |    12      |
|  2   |   NULL  |    13      |
|  3   |   NULL  |    14      |
+------+---------+------------+

I was expecting to get only NULL in the first row. But I am getting NULL in all rows. Please explain why it's giving NULL in all rows.

[Note](https://dev.mysql.com/doc/refman/8.0/en/user-variables.html): "Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL." If you're on MySQL 8.0+, `SELECT c1, LAG(c2) OVER() AS c2 FROM t1;` is both way more legible, and also future-proof. — Amadan, Mar 04 '19 at 08:26
Ya, it work but my doubt is only how I am getting NULL in rows. — T.g, Mar 04 '19 at 08:38
@T.g - The behavior is just undefined and depends on the used version and it's engine/optimizer. — Paul Spiegel, Mar 04 '19 at 08:42

Paul Spiegel · Accepted Answer · 2020-10-05T08:44:47.677

The reason for the NULLs is documented here:

Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement.

Since @prev is not defined before it is read for the first time, we cannot even say, what type it has.

Now you could preset it to some value like set @prev = 0;. But 0 might be a valid value, so you probably want it to be NULL for the first row. set @prev = null; will neither work. But you can do the following:

set @prev = -1;   -- define type as SIGNED
set @prev = null; -- set NULL

Now you query should return

c1  @prev   @prev:=c2
1   null    12
2   12      13
3   13      14

You can also define the type and assign NULL in a single statement:

set @prev = cast(null as signed);

See Demo

However - When you read and write a user variable in the same statement (other than SET) - the behavior is not defined. In the documentation you will find the following statements:

It is also possible to assign a value to a user variable in statements other than SET. (This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.)

...

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.

...

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed.

I've marked the important parts in bold, so you can see that it's not recommended to use the variables in that way.

Newer versions (MySQL 8.0 and MariaDB 10.2) now support window functions like LEAD() and LAG(). So you can write the query the following way:

Select c1, c2, lag(c2) over (order by c1) as prev
from t1

Demo

This is not only reliable for future MySQL versions. It will also work on many (all major) RDBM-systems.

score 1 · Answer 2 · answered Mar 04 '19 at 08:16

1

You can try below -

DEMO

SET @prev=-1;
Select c1,@prev prev_col,@prev:=c2 cur_col
from t1 order by c1

OUTPUT:

c1  prev_col    cur_col
1     -1          12
2     12          13
3     13          14

answered Mar 04 '19 at 08:16

Fahmi

37,315
5
22
31

Nick · Answer 3 · 2019-03-04T09:27:56.647

The reason you're getting all NULL values is because you aren't initialising your @prev variable. You can initialise it with a SET @prev := 0 statement or, if you need to get this result with only a single query, you can use a CROSS JOIN to initialise your variable:

SELECT c1, @prev AS prev, @prev:=c2 AS c2
FROM t1
CROSS JOIN (SELECT @prev := 0) v;

Output:

c1  prev    c2
1   0       12
2   12      13
3   13      14

If you don't have to use a variable, and your c1 values are consecutive, you can also get the same result using a LEFT JOIN:

SELECT t1.c1, t1.c2, t2.c2 AS prev
FROM t1
LEFT JOIN t1 t2 ON t2.c1 = t1.c1 - 1
ORDER BY t1.c1

If your c1 values might not be consecutive, the above LEFT JOIN won't work properly. In that case you need to JOIN on the value of c1 which is the highest value less than the value to be JOINed to:

SELECT t1.c1, t1.c2, t2.c2 AS prev
FROM t1
LEFT JOIN t1 t2 ON t2.c1 = (SELECT MAX(c1) 
                            FROM t1 t3 
                            WHERE t3.c1 < t1.c1)
ORDER BY t1.c1

In both cases the output is the same:

c1  c2  prev
1   12  null
2   13  12
3   14  13

Demo on dbfiddle

Note that the second snippet will do something very different if the values in `c1` are not contiguous. — Amadan, Mar 04 '19 at 08:47
@Amadan you are absolutely correct, thanks for pointing it out. I've made a note about that and added a query that will work in that scenario too. — Nick, Mar 04 '19 at 09:29

forpas · Answer 4 · 2019-03-04T11:49:22.060

You can get the previous value with a subquery:

    SET @def = 0;

    Select 
      t.c1,
      coalesce(
        (select c2 from tablename where c1 < t.c1 order by c1 desc limit 1), 
        @def) prev_col,
      t.c2 cur_col
    from tablename t order by t.c1;

See the demo Or:

SET @def = 0;

Select 
  t.c1,
  coalesce(
    (select max(c2) from tablename where c1 < t.c1), 
    @def) prev_col,
  t.c2 cur_col
from tablename t order by t.c1;

See the demo

Getting values from previous rows using user-defined variable in MySQL

4 Answers4