Difference of each row from the next row of one table

[SMB]

Dear All,

Suppose I have a table with the following data

Date Rate

01-01-07 255
01-02-07 250
01-03-07 260
01-04-07 255

I want to write a query which calculates the rate difference in each row and
the output should be like this

Date Rate Difference

01-02-07 250 -5
01-03-07 260 10
01-04-07 255 -5

It means that the difference of rate in current date from the rate in
previous date should be the third column.

WHAT THE QUERY SHOULD BE ???????

Please write the select statement.

Thanks

 

[Allen Browne]

Use a subquery to get the value from the previous record.

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

The example is for meter readings (current value less previous reading),
which is similar to what you need.

 

[SMB]

Dear Allen Browne,

Thank you very much. I got the solution of my problum.

I will study your web site later on to get more tips.

Thanks again.
Shaukat