Compute difference using the same field but different dates

[Teg]

Good day (=

Say I have the ff table structure:

[tt]
+--------+---------+--------+
| c_name | c_price | c_date |
+--------+---------+--------+
[/tt]

Now I want to compute the difference between the latest entry of [c_price] minus the second to the latest entry of [c_price]

Would it be possible to compute for the difference using ONE sql statement. Right now I'm using two

 

[fluteplr]

select ffnew.price newprice, ffold.price oldprice, ffnew.price - ffold.price difference
from ff ffnew, ff ffold
where ffnew.name = ffold.name and
ffnew.cdate in (select max(c_date) from ff where ffnew.c_name = ff.c_name) and
ffold.cdate in (select max(cdate) from ff where ffnew.c_name = ff._name and ff.c_date < ffnew.c_date)

This assumes only one entry per cname per c_date.

If the tables are any reasonable size you are going to want an index on c_name and c_date or the performance is going to suck.

 

[Teg]

Thanks for the solution fluteplr