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.
MySQL Server

Avatar of undefined
Last Comment
skullnobrains

8/22/2022 - Mon
Derek Jensen

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ibradshaw

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
skullnobrains

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