return the row where before and after value is lower

April 19, 2022

I have a table that looks like this:

name         val
1            1
2            2
3            1
4            2
5            2
6            10

For each row, I want to check if there’s an increase in the value as compared to the last value. Then, I want to return the rows where the previous and next value is lower. For example, in this case, I want to return

name         val
2            2

but not any of the following:

4            2
5            2
6            10

since there’s a constant increase till the end of the table and no decrease at the end. Ideally, I would also like to order the table by the namecol before I start comparing the val.

>Solution :

you can write a query using lead and lag functions like below

select name, val 
from 
(select *,
 CASE WHEN LAG(val) OVER( ORDER BY name) IS NULL THEN 
val ELSE LAG(val) OVER( ORDER BY name) END asd prev_value,
    CASE WHEN LEAD(val)  OVER( ORDER BY name) IS NULL THEN val ELSE LEAD(val)  OVER( ORDER BY name) END as next_value
    from yourtable 
    )T
    where prev_value<val AND val> next_value