Q

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.

This was first published in September 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close