Hi everybody !! I have problem again.
This is my database(Microsoft Access)
dat ti price
9/15/2004 10:17:20 12
9/15/2004 10:17:28 32
9/15/2004 10:17:31 11
9/16/2004 10:17:40 12
9/16/2004 10:17:45 32
I want to write sql that show the result of campare the last two price. For example, 32-12=20 I want the result to show 20.
I can only assume that you want to create something that takes the last two values and shows the difference.
In short. SQL can't but your chosen DB might have other tricks.
Your best bet would be to sort by date and "SELECT TOP 2 * from tablename..." and then read the values out into your program and compair them before showing the results. What your asking for is not really an agragate function that is it is not a summary, statistic or conditional data set of the whole... mmm that's not a very good explination, sorry...
A DB is a set of data and everything you return is either a sub-set of data or data about data. What you want is a-b=c and that is a function. While SQL has some support for functions it is quite limited and what you want sounds like it might need to itterate (loop).
However there is hope for you.
If your are useing SQL server a stored procdure will do the job but you will need a fresh post over in the right forum
If you are useing Access then VBa can handle the work at "run time" and you will need a fresh post in the VBa / Access section.
If you are useing MySQL or some other DB you can probably use one or both answers and you will need to ask the relivant platform specifc question in the forum of your choice.
Sorry that I couldn't answer more short windedly or give the finnished code or anything but I'm just in the mood to pepper my answer with answers to sub questions that I might not be arround to answer.
I don't know if this will work in ACCESS (or any other DBMS), but could you do it something like this? (I don't know the datatype of the dat and ti columns but you would have to concatentate them somehow to compare the date/time combination.)
select max(price) - min(price)
from (select price from table1 t1
where 2 > (select count(*)
from table1 t2
where date(t1.dat||t1.ti) < date(t2.dat||t2.ti)))