0
318
ViewsHow to compare values in same table column and return if equal
I have the following data 
I need to extract and return rows of data whenever cs3Horizontal for a row = same column but next row. For example, in the picture you see that cs3Horizontal = 65 for rows 85/86, so return those rows.
I have looked at numerous options using OVER, LEAD and LAG but to be honest the documentation just does not provide enough detail for somebody who has never used these window functions before. I think I am looking at the right solution, but how do I implement it?
I'm using PostgreSQL 9.2.9, compiled by Visual C++ build 1600, 64-bit. The table in question is time series data and as such, the first column is primary key.
·
Santiago Trujillo
1 answers
Answer question0
LEAD() and LAG() are the solutions. If you want both rows:
select t.*
from (select t.*,
lag(cs3Horizontal) over (order by cs3time) as prev_cs3Horizontal,
lead(cs3Horizontal) over (order by cs3time) as next_cs3Horizontal
from t
) t
where prev_cs3Horizontal = cs3Horizontal or
next_cs3Horizontal = cs3Horizontal;
·
Santiago Trujillo
Report
Answer question