MySQL Forums
Forum List  »  General

Re: Sum values only if successor is lower
Posted by: Peter Brawley
Date: April 17, 2015 10:20AM

Assuming, in your example, that the ordering you show is given in table t by a sequential id column...

id,value
~~~~~~~~
1, 100
2, 120
3, 140
4, 110
5, 130
6, 105
7, 110
8, 80

Then we find value-successor pairs with ...

select a.value, b.value as successor
from t as a
join t as b on b.id=a.id+1
order by a.id;

...so the pairs and diffs where value > successor are...

select value, successor, value-successor as diff
from (
  select a.value,b.value as successor
  from t a
  join t b on b.id=a.id+1
  order by a.id
) p
where value > successor;

If id isn't sequential, use something like this as the subquery to find successors...

select value, (select min(value) from parent p where p.id>parent.id) as successor
from parent
order by id;

For other ways to find previous/next rows see "Find previous and next values in a sequence" at http://www.artfulsoftware.com/queries.php



Edited 1 time(s). Last edit at 04/17/2015 10:23AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Sum values only if successor is lower
April 17, 2015 10:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.