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.
This was first published in September 2005