How to fetch current row by checking if it is greater than lead and lag

March 5, 2022

I have a table company with two columns.

ID	VALUE
1	1
2	2
3	2
4	2
5	1
6	2
7	1

I have to pick the records from the table that are greater than its lead & lag.
I tried to implement lead and lag function over the table as below.

select ID, 
lead(ID, 1) over (order by ID) as nextVal, 
lag(ID, 1) over (order by ID) as preVal 
from company;

What I don’t understand is how can I compare my current row with lead & lag and then select only that current row if it is bigger than its lead & lag

I am looking for this record:

ID
6

First row and last can’t be considered because there is no previous row for the first record and next row for the last record. Could anyone let me know how can I achieve this ?

>Solution :

Try this:

with u as
(select ID, VALUE, 
lead(VALUE) over (order by ID) as nextVal, 
lag(VALUE) over (order by ID) as preVal 
from company)
select ID from u
where VALUE > nextVal and VALUE > preVal;

Fiddle

It’s lead(VALUE) not lead(ID), same for lag.