Avatar of ibradshaw
 asked on

Subtract values between rows


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:


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


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,

MySQL Server

Avatar of undefined
Last Comment

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


"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.



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
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


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.


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

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