Id like to run a query that would be very easy to do using arrays in a imperative language using the array index. I have the following table:
value id
10 x1
20 x2
15 x3
25 x4
30 x5
31 x6
I wish to calculate the difference of pair of vicinal values like:
value
20 - 10
25 - 15
31 - 30
I only know that x6>x5>...>x1. I have no clue on how to do this using MySQL.
Assuming you have a way of ordering your data you could generate row numbers and join the odd numbered rows to the even numbered rows
drop table if exists t;
create table t(value int, id varchar(2));
insert into t values
(10 , 'x1'),
(20 , 'x2'),
(15 , 'x3'),
(25 , 'x4'),
(30 , 'x5'),
(31 , 'x6');
select s.*,a.*
from
(select id, value ,
@rn:=@rn + 1 rn
from t
cross join (select @rn:=0) r
order by id
) a
join
(
select id, value ,
@rn1:=@rn1 + 1 rn1
from t
cross join (select @rn1:=0) r
order by id
) s
on s.rn1 = rn + 1
where a.rn % 2 > 0
;
+------+-------+------+------+-------+------+
| id | value | rn1 | id | value | rn |
+------+-------+------+------+-------+------+
| x2 | 20 | 2 | x1 | 10 | 1 |
| x4 | 25 | 4 | x3 | 15 | 3 |
| x6 | 31 | 6 | x5 | 30 | 5 |
+------+-------+------+------+-------+------+
3 rows in set (0.00 sec)
