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
