This is my 2 cents. Maybe there's better way to do this:
select
id,
amount,
(select amount from tablename where id=t.id and sno < t.sno limit 1)
as oldAmount
from tablename t
br
Radzi.
On 14-Jan-2008, at 8:14 PM, Vishal Mailinglist wrote:
Hi
sno | id | amount
1 | 1 | 200
2 | 1 | 300
3 | 2 | 100
4 | 2 | 100
5 | 1 | 500
how could I subtract the sno 1 id 1 and sno 2 id 1 amount .
select
(select amount from tableName where sno=1 and id=1) -
(select amount from tableName where sno=2 and id=1);
What if I do not have control over sno i.e it is random or
unpredictable , I want to subtract it in order of occurrence. Like
doing subtracion of sno 2 and 5 and so on may be next occurance for
id 1 is at sno 20 , then what.
I don't understand what you are trying to achieve. What would be the
correct output on your example?
The table is tracking price change of a commodity that is in id
column . We
need to calculate rise or fall of that commodity as per the last
price . and
the second last price and latest price keep changing like
<code>
sno | id | amount
1 | 1 | 200 <<< when I run query this is the first instance
of the
commodity for id 1
2 | 1 | 300 <<< the first instance would be subtracted with
the
second instance here
3 | 2 | 100
4 | 2 | 100
5 | 1 | 500 <<< finally the fourth instance would be calculated
w.r.tthird for id 1
</code>
and So on , now since sno is autoincrement I do not have control
over sno
and cannot run query w.r.t sno i.e I cannot predic in the table when
comodity price would change and at what serial number it would be.
Hoping I was success full this time , thanks for your time and
patience .
--
Regards,
Vishal Kashyap.
Need help visit
http://help.vishal.net.in
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
