CODEWITHSUNDEEP
mysqlsql
Junior
Junior

Reputation: 11990

How do I store a value from the last row in a variable using MySQL?

I am trying to calculate the date difference between each record in a dataset for each account.

Here is the data that I have

id  aid value
1   1   2015-01-01
2   1   2015-01-07
4   1   2015-01-08
6   1   2015-04-10
3   2   2015-02-01
5   2   2015-02-05

I would first need to combine the data where I can use TIMESTAMPDIFF to calculate the difference in Days (i.e. TIMESTAMPDIFF(DAY, previousValue, currentValue)).

How can I combine the rows in the dataset about to look like this

aid  currentValue  previousValue
1    2015-01-07    2015-01-01
1    2015-01-08    2015-01-07
1    2015-04-10    2015-01-08
2    2015-02-05    2015-02-01

from there I can easily calculate the difference in days between current and previous value.

Note, that I have a large data set and I can't use subqueries in my select this is why I need to know how to do it using variables.

How can convert my initial dataset to the second dataset where I have currentValue, previousValue for each account?

Here is SQL to generate tables with the data above

CREATE TEMPORARY TABLE lst
(
    id int,
    account_id int,
    value date
);


INSERT INTO lst VALUES
  (1, 1, '2015-01-01')
, (2, 1, '2015-01-07')
, (3, 2, '2015-02-01')
, (4, 1, '2015-01-08')
, (5, 2, '2015-02-05')
, (6, 1, '2015-04-10');


CREATE TEMPORARY TABLE lst1 AS 
SELECT * FROM lst ORDER BY account_id, value ASC;

UPDATED

This is what I get after attempting Giorgos Betsos' answer below

'1', '2015-01-01', '2015-01-07'
'1', '2015-01-07', '2015-01-08'
'1', '2015-02-05', '2015-04-10'
'2', '2015-01-08', '2015-02-01'
'2', '2015-02-01', '2015-02-05'

Upvotes: 1

Views: 929

Answers (1)

Daniel E.
Daniel E.

Reputation: 2480

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE test
(
    id int,
    account_id int,
    value date
);


INSERT INTO test VALUES
  (1, 1, '2015-01-01')
, (2, 1, '2015-01-07')
, (3, 2, '2015-02-01')
, (4, 1, '2015-01-08')
, (5, 2, '2015-02-05')
, (6, 1, '2015-04-10');

Query 1:

SELECT
IF(@accId = account_id, @prevDate, '-') as "Previous Date",
(@prevDate := value) as "Date",
 (@accId :=account_id) as account_id
FROM 
test, (SELECT @accId := 0) a, (SELECT @prevDate := '-') b
ORDER BY account_id ASC, value ASC

Results: 

    | Previous Date |       Date | account_id |
    |---------------|------------|------------|
    |             - | 2015-01-01 |          1 |
    |    2015-01-01 | 2015-01-07 |          1 |
    |    2015-01-07 | 2015-01-08 |          1 |
    |    2015-01-08 | 2015-04-10 |          1 |
    |             - | 2015-02-01 |          2 |
    |    2015-02-01 | 2015-02-05 |          2 |

Upvotes: 2

Related Questions