Select to subtract current value from previous and return column with result

Question

I have two tables: Samples and Values, which have columns as follows:

TABLE Samples

Amostra_id 
Data 
Operador_Id
Carta_Id

TABLE Values

Amostra_Id 
Valor

As you can see, the Values table relates to the Samples table from Sampling. Operador_id and Carta_id comes from other tables that is not the case.

What I need is this: I need to have one SELECT that gives me as a return to Date, Value and in addition a column called AMPLITUDE. AMPLITUDE is the value minus the previous value.

I will try to exemplify:

VALOR     l 60   l 50   l 30   l 80   
AMPLITUDE l 10   l 20   l -50  l 

Note: Mysql database.

---

I tried the following to get a column dislocated from the other one (I did this, because then I calculate the amplitude from these columns):

Select * from(
         select *, curRank := @curRank + 1 as rank
         from(
              select * from valores
              left join (amostras)
                     on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
              order by amostras.AMOSTRAS_ID desc
          ) t, (select @curRank := 0) r
          where t.CARTA_CARTA_ID = 2
 )
 left join 
 (
         select u.valor as value2, @curRank  := @curRank + 1 as rank from
         (
                select * from valores
                left join (amostras)
                       on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
                order by amostras.AMOSTRAS_ID desc
         ) u, (select @curRank :=0) r
         where u.CARTA_CARTA_ID = 2
         limit 1,25
  ) x
  on (v.rank like x.rank)

I had forgotten to explain, but my select should only return the last 25 values - so I used LIMIT.

In this Select I did, it seems to be very close, but the data of the second select (value2 and rank - of the left Join) returns everything as NULL. Maybe my mistake is in using left Join.

Any idea? Grateful.

Answer 1

It is possible to do with a variable:

select *, ( v.valor - @lastValue ) as amplitude,
      @lastValue := v.valor
   from
      valores v,
      ( select @lastValue := 0 ) SQLVars
   order by
      v.amostra_id

sqlfiddle: http://sqlfiddle.com/#! 2/e2ea86/1

Answer 2

Finally I got it with the code below:

select d.rank, d.DATA, d.VALOR, z.VALOR2, ABS(d.VALOR - z.VALOR2) AMPLITUDE
from (
    select * from
       (
        select *, @curRank :=@curRank + 1 as rank
        from 
        (
            select * from valores
            left join (amostras) 
            on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
            ORDER BY amostras.DATA desc
        ) a, (select @curRank :=0) b
        where a.CARTA_CARTA_ID = ? (id desejado aqui)                
        limit 0,25 (limitando nas 25 amostras)
       ) c
     ) d
(select VALOR2, rank as r from(
     select v.valor as VALOR2, @curRank := @curRank + 1 as rank
     from(
          select * from valores  
          left join (amostras)
          on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
          order by amostras.DATA desc   
         ) v, (select @curRank :=0) x
      where v.CARTA_CARTA_ID = ? (id desejado aqui)
      limit 1,25) y
 ) z
 where z.r like d.rank

That’s right!