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 ?
CodePudding user response:
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;
It's lead(VALUE)
not lead(ID)
, same for lag
.