We help IT Professionals succeed at work.

Subtract values between rows

ibradshaw
ibradshaw asked
on
518 Views
Last Modified: 2012-05-30
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.
Comment
Watch Question

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. :)

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.