return the row where before and after value is lower

Question

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.

Answer 1

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

Explanation:

So lag gets data for Val column from preceding row and lead gets from next row

We add a case statement to handle null values for first row's lag and last row's lead data. To know more about the lead lag functions read up on this link

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

Using default values

select name, val 
from 
(select *,
 LAG(val,1,val) OVER( ORDER BY name) as prev_value,
 LEAD(val,1,val) OVER( ORDER BY name) as next_value
    from yourtable 
    )T
    where prev_value<val AND val> next_value