How to get previous record value from MySql Query

Question

I want to get previous record value to calculate with current value;

example: current value of col3 will be = (previous col3 value + current col2 value)

Note: It is not important to maintain col1 order. Because the resulted records are fetched by Select query.

col1 | col2 | col3
------------------
a    | 1    | 1
b    | 2    | 3  <= 1+2
a    | 1    | 4  <= 3+1
d    | 3    | 7  <= 4+3

I think it is not so hard but I can not solve the problem using mysql

How do you know which row is the "previous" one? For example, if `col1` is `d` would the "previous" row have a `col1` of `c`? — Martin, Aug 16 '13 at 12:23
Possible duplicate [Calculate a running total in MySQL](http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql) — Noel, Aug 16 '13 at 12:24
No matter which record is previous and which is its value. When the query will be fetched the result should be like above.@Martin — Iqbal Hossain, Aug 16 '13 at 12:27
@IqbalHossain I think you are mistaken unfortunately. There is no guarantee of row order if you have not specified an order clause, and therefore you cannot assume that the rows will always be in the above order. — Martin, Aug 16 '13 at 12:30
@Martin: Why do you think about order. I just gave an example. It is no matter the order may changed anytime.. but col3 will be calculated with its previous one fetched value. — Iqbal Hossain, Aug 16 '13 at 12:32
@Praveen: No there is no primary key. The resulted records are from Select Query. — Iqbal Hossain, Aug 16 '13 at 12:38

score 4 · Accepted Answer · answered Aug 16 '13 at 12:37

Assuming a starting table like this:

mysql> SELECT * FROM test;
+------+------+
| col1 | col2 |
+------+------+
| a    |    1 |
| b    |    2 |
| c    |    1 |
| d    |    3 |
+------+------+

... you can get the result you want by doing this:

mysql> SELECT col1, col2, @a := @a + col2 AS col3 
    -> FROM test JOIN (SELECT @a := 0) t
    -> ORDER BY col1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    |    1 |    1 |
| b    |    2 |    3 |
| c    |    1 |    4 |
| d    |    3 |    7 |
+------+------+------+
4 rows in set (0.00 sec)

If you reverse the order, you get the results according to your problem statement, i.e. col2 plus the previous col3 in the order displayed:

mysql> SELECT col1, col2, @a := @a + col2 AS col3 
    -> FROM test JOIN (SELECT @a := 0) t
    -> ORDER BY col1 DESC;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| d    |    3 |    3 |
| c    |    1 |    4 |
| b    |    2 |    6 |
| a    |    1 |    7 |
+------+------+------+
4 rows in set (0.00 sec)

It should be `@a := @a + col2 col3` i think. – Vatev Aug 16 '13 at 12:39 — Vatev, Aug 16 '13 at 12:39

score 2 · Answer 2 · edited Aug 30 '17 at 06:56

2

I think this solution might do what you want.

set @a = null;
SELECT @a as previousCol2value , col1, col2, @a := col2 ignoreThis
    FROM Table1 ;

Check the SQL FIDDLE below if you want to see it in action:

http://www.sqlfiddle.com/#!2/774ff/42/1

edited Aug 30 '17 at 06:56

zarruq

2,445
2
10
19

answered Mar 28 '14 at 17:20

Sandro Frattura

41
1

Thanks, I like the avoidance of a join. – William T. Mallard Nov 25 '16 at 22:54

score 1 · Answer 3 · answered Aug 16 '13 at 12:43

1

SELECT col1, col2, @a := @a + col2 col3 
    FROM Table1 JOIN (SELECT @a := 0) t;

FIDDLE

answered Aug 16 '13 at 12:43

Praveen Prasannan

7,093
10
50
70

How to get previous record value from MySql Query

3 Answers3

Linked