Compare values in consecutive rows
I have a set of data in PostgreSQL with reading_timestamp as one of the columns and level as another column. I need to sort these data in ascending order (based on reading_timestamp) and then compare the value of level in two consecutive rows. If the difference of level is more than 200 I need to select that row.
I have a set of data in PostgreSQL with reading_timestamp as one of the columns and level as another column. I need to sort these data in ascending order (based on reading_timestamp) and then compare the value of level in two consecutive rows. If the difference of level is more than 200 I need to select that row.
For example, let my data be:
reading_timestamp level (1) 06/09/2005 1000 (2) 07/09/2005 990 (3) 08/09/2005 1300 (4) 09/09/2005 1010
After computing row 2 - row 1, row 3 - row 2, row 4 - row 3, only row 3 is selected because 1300 - 990 > 200. Please help me to write a query for this. Thanks.
This can be achieved with a self-join.
select rowN.reading_timestamp
, rowN.level
from daTable as rowM
inner
join daTable as rowN
on rowN.reading_timestamp =
( select min(reading_timestamp)
from daTable
where reading_timestamp
> rowM.reading_timestamp )
where rowN.level - rowM.level > 200
The JOIN condition joins each row to the "next" row which is determined as being the row with the lowest reading_timestamp that is higher than the current row's reading_timestamp.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments