Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: sensor data analysis

    Hi!
    I am not an SQL programmer at all but I am trying to solve this problem for some data analysis.

    I have two tables (meter, sensor).

    The meter table contains values against a timestamp (epochtime), e.g.

    record | timestamp | value_a
    1 100001 5.4
    2 100008 7.3
    3 100015 6.4

    The sensor table contains values taken at more intervals, e.g.

    record | timestamp | value_b
    1 100001 35
    2 100003 36
    3 100004 37
    4 100006 36
    5 100008 39
    6 100010 40
    7 100011 41
    8 100015 35
    9 100017 36
    10 100020 36

    What I need to do is to list value_a and value_b together in this way

    timestamp | value_a | value_b
    100001 5.4 35
    100003 5.4 36
    100004 5.4 37
    100006 5.4 36
    100008 7.3 39
    100010 7.3 40
    100011 7.3 41
    100015 6.4 35
    100017 6.4 36
    100020 6.4 36

    I would be grateful for any hint of how I can crack this problem!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT sensor.timestamp
         , meter.value_a
         , sensor.value_b 
      FROM sensor
    LEFT OUTER
      JOIN meter
        ON meter.timestamp =
           ( SELECT MAX(timestamp)
               FROM meter
              WHERE timestamp <= sensor.timestamp )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    2

    It works.

    Many thanks for this quick and accurate help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •