CODEWITHSUNDEEP
sqloracle-databaseselect
user1885304
user1885304

Reputation: 65

Oracle query - without cursor

I got stuck with this query. I have table with data samples/records from sensors like this:

sensor | datetime       | value
----------------------------------
A      | 20140512 12:00 | 100
A      | 20140512 12:02 | 102
A      | 20140512 12:04 | 104
B      | 20140512 12:00 | 200
B      | 20140512 12:02 | 203
B      | 20140512 12:04 | 206
C      | 20140512 12:00 | 300
C      | 20140512 12:02 | 304
C      | 20140512 12:04 | 308

And I need to get value and previous value (alternatively increase/decrease) in given time (specified by variable) like this (time 12:02):

sensor | value | previous value
--------------------------------
A      | 102   | 100
B      | 203   | 200
C      | 304   | 300

Could anyone please help me?

Note: I dont want do use temporary cursors and teporary tables.

Thanks, Pavel

Upvotes: 0

Views: 104

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You should use analytic functions for this, in particular, lag():

select t.sensor, t.value, t.prev_value
from (select t.*,
             lag(value) over (partition by sensor order by datetime) as prev_value
      from table t
     ) t
where datetime = :DATETIME;

Upvotes: 1

MatBailie
MatBailie

Reputation: 86735

A really generic (ANSI-SQL) query could be as follows.

SELECT
  this.*,
  prev.*
FROM
  yourTable  AS this
INNER JOIN
  yourTable  AS prev
    ON  prev.sensor   = this.sensor
    AND prev.datetime = (SELECT MAX(lookup.datetime)
                           FROM yourTable  AS lookup
                          WHERE lookup.sensor   = this.sensor
                            AND lookup.datetime < this.datetime
                        )

Other alternatives depend on which version of Oracle you're on.

Upvotes: 0

Related Questions