On Jan 14, 2008 9:09 AM, Vishal Mailinglist <[EMAIL PROTECTED]> wrote: > > sno | id | amount > > > 1 | 1 | 200 > > > 2 | 1 | 300 > > > 3 | 2 | 100 > > > 4 | 2 | 100 > > > 5 | 1 | 500 > 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.
The simple solution is to select both rows separately and then subtract it in your application. Really, if it were me I'd do that. Or you can use sum(), the only problem is how to make the the amount to be subtracted negative. Something like below, where the amount with the lower sno is turned negative. select sum(case when sno=(select min(sno) from tableName where (sno=? and id=?) or (sno=? and id=?)) then -amount else amount end) from tableName where (sno=? and id=?) or (sno=? and id=?) M. Manese ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
