MySQL Find Peak Values Where Previous Value Is Lower?

Question

I have an integer field and a datetime field. Is there a query that could find all peak values or where there is a transition from low to high? I don't think I can simply use GROUP BY because the datetime values are too inconsistent. The 'peaks' can happen at anytime (within the minute, hour, day, month etc.).

For example I need all values circled in red from the image.

Answer 1

You want data points whose value is greater than the "following" value. You can use lead(). Assuming that your table has columns date and value, you would do:

select *
from (
    select t.*, lead(value) over(order by date) lead_value
    from mytable t
) t
where value > lead_value