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
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.