Link to home
Start Free TrialLog in
Avatar of ibradshaw
ibradshaw

asked on

Subtract values between rows

Hi,

I've got some data populated in a table and I would like to calculate the difference between row values in a particular order.

The data is like this:

uid(int),count(int)
1,5
2,7
3,15
4,10
5,12

so I would like to do a select which provides a list of differences and the second value uid:

2,2
3,8
4,-5
5,2

Is there any way to do this in SQL? I'm guessing I need to do some sort of self join, but ?

I want to do this in SQL if possible as there will be thousands of rows so extracting it all into a script and calculating it would be slow.

Thanks for you help,

Ian.
Avatar of Derek Jensen
Derek Jensen
Flag of United States of America image

Not necessarily. The more operations/refinements SQL has to do on a select will slow it down on an exponential scale, but...in general, you're right. You may just want to run some speed tests each way and check which is faster. If the SQL returns in less than 0.05, chances are, it'll be faster regardless...

To answer your question, I don't think you can do it in just SQL. How can you guarantee that 1) the row order returned will be correct/the same, and 2) that you're grabbing the needed row? If you did a self-join, well, I don't see how you could grab the *next* row that the left half of the join is going to be grabbing on its *next seek*, etc. etc...do you see the problem?

It would definitely be easier, complexity-wise, to simply grab the result set--and, in fact, returning * is faster than returning a subset of columns, as the SQL engine has to perform far fewer seeks--copy that result set, and then loop through both arrays, nested-foreach style, offset by one. :)
Avatar of ibradshaw
ibradshaw

ASKER

Hi,

"How can you guarantee that 1) the row order returned will be correct/the same, and 2) that you're grabbing the needed row?"

Yep ... thats my problem ... I can't work out how to, or if you can.

I'll do it in a script for now, but if anyone has any ideas how to do it direct then that would be great.

Cheers

Ian.
ASKER CERTIFIED SOLUTION
Avatar of skullnobrains
skullnobrains

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

That works brilliant, thank you. Got an auto-inc on the uid so happy days.

Extra column might be the answer in the future, but this wil be handy for lots of other things too.

Cheers

Ian.
beware it will not work if you delete rows i.e. if you end up with gaps in the set, because the autoinc will not reassign any value that it already has assigned. that is also true when you delete the last row and recreate it.

if your dataset is not too big, you may use a subquery to generate you table with an extra row_index column, and join that subquery resultset on itself

happy coding anyway