MySql moving differences?

Question

Suppose I have this code

create temporary table somedata (a integer);
insert into somedata (a) values (11), (25), (62); --#these values are always increasing
select * from somedata;

giving this

+--+
|a |
+--+
|11|
|25|
|62|
+--+

How do I calculate a column of values 'b' where each one is the difference between the value of 'a' in the current row and the value of 'a' in the preceding row?

+--+--+
|a |b |
+--+--+
|11| 0| # ie 11-11 since theres no preceding row
|25|14| # ie 25-11
|62|37| # ie 62-25 etc
+--+--+

This is so obvious in openoffice or excel that I feel a bit silly not having yet found how to do this on MySql's site nor anywhere else.

Answer 1

It would easier if you had an auto_increment column to give each row its own id - then you could join the table against itself on (alias2.id=alias1.id+1) and calculate the diff from alias2.a-alias1.a

As it is, I think the only way is with a subquery to obtain the largest value a less than the current row value, which will be very inefficient.

So if you can, modify your schema!

create table somedata (
    id int auto_increment not null, 
    a integer, 
    primary key(id)
);
insert into somedata (a) values (11), (25), (62); 

select a2.a,ifnull(a2.a-a1.a, 0) as diff  
from somedata  as a1 
right join somedata  as a2 on (a2.id=a1.id+1);

+------+------+
| a    | diff |
+------+------+
|   11 |    0 |
|   25 |   14 |
|   62 |   37 |
+------+------+

Use inner join rather a right join if you don't want that first zero result.

Edit: see this article for a fuller walkthrough of this idea: Calculating differences between successive rows