How can I increment a row in a SELECT Statement?

Question

is it possible to increment last row with a constant value? I would like to receive a response like this:

Date        |  Value 
-------------------
2013-11-01  |  14.32
2013-11-02  |  15.32
2013-11-03  |  16.32
...         |  ...

I've tried it with Variables:.

SET @INCREMENT := '1.00';
SET @VALUE := '14.32';
SELECT `Date`, @VALUE + @INCREMENT
FROM `table` 
WHERE `Date` > '2013-11-01'

but this shows only one constant value in row2.

Can you post an example data (www.sqlfiddle.com) so we can see what you mean instead we need to guess? — Raymond Nijland, Dec 04 '13 at 22:43

Gordon Linoff · Accepted Answer · 2013-12-05T21:46:09.200

1

I think you want to reset the value for each row:

SET @INCREMENT := '1.00';
SET @VALUE := '14.32';
SELECT `Date`, (@VALUE := @VALUE + @INCREMENT) as value
FROM `table` 
WHERE `Date` > '2013-11-01';

Normally when you do this, you would have an order by clause to ensure that the rows are returned in the order you want them to be.

edited Dec 05 '13 at 21:46

answered Dec 04 '13 at 22:39

Gordon Linoff

Oh, yes, that's great. Value ist incresing by Increment. I just inserted an @ in line 3: SELECT `Date`, (@VALUE := @VALUE + @INCREMENT) as value – Peter Dec 05 '13 at 20:09

Raymond Nijland · Answer 2 · 2013-12-04T22:52:06.487

Not sure if you mean this, but this seemed to be the most logical.. Assuming you have an existing table with Date and Value

SET @INCREMENT = 1.00;

SELECT
    Date "date"
 , (Value + @INCREMENT) "value"
FROM 
  table 
WHERE
  Date > '2013-11-01'
ORDER BY
  Date ASC

Or even better (because you dont need the "overhead" off an user variable to calculate this on runtime)

SELECT
    Date "date"
 , (Value + 1.00) "value"
FROM 
  table 
WHERE
  Date > '2013-11-01'
ORDER BY
  Date ASC

2 Answers2