continuous data comparison

[rostien]

i have a table with fields: p,x,y,z,date
p being the common field with multiple values. for example:
p x y z date
100 2 2 2 oct 1 2011
100 3 3 3 oct 2 2011
100 3 4 5 oct 3 2011
100 4 4 4 oct 4 2011
how can i make a query to compare the data by the latest date to the previous date continuously as more data is added????
oct 4- oct3
p x y z
100 1 0 -1
oct 3- oct2
p x y z
100 0 1 2

PLEASE ACCESS GODS!!! I kneel in the shadow of your excellence.... HELP!!!

 

[Taruz]

Hi..

can use this query..:

select  
           p, x, y, z, date, 
           x-(select last(x) from table_name where date<trz.date) as Xdifference, 
           y-(select last(y) from table_name where date<trz.date) as Ydifference, 
           z-(select last(z) from table_name where date<trz.date) as Zdifference

from table_name as trz

 

[rostien]

brilliant..
now how can i seperate the comparison so it will only compare the same p

 

[Taruz]

brilliant..
now how can i seperate the comparison so it will only compare the same p

add this way..

select  
           p, x, y, z, date, 
           x-(select last(x) from table_name where date<trz.date[COLOR="Red"] and p=trz.p[/COLOR]) as Xdifference, 
           y-(select last(y) from table_name where date<trz.date[COLOR="Red"] and p=trz.p[/COLOR]) as Ydifference, 
           z-(select last(z) from table_name where date<trz.date [COLOR="red"]and p=trz.p[/COLOR]) as Zdifference

from table_name as trz

 

[rostien]

it works but if i change a date in the table the query doesn't update.. all the diff are now related to the 1st record?? do you know why??