In the simple case where each box/timestamp pairing is guaranteed to have both foo and bar parameters:

\nselect a.box,\n       a.param_value foo_value,\n       b.param_value bar_value,\n       a.timestamp\n  from some_table a,\n       some_table b\n where a.box = b.box\n       and a.timestamp = b.timestamp\n       and a.param_name = 'foo'\n       and b.param_name = 'bar'\n

In the case where foo is guaranteed to be there and bar is optional:

\n   select a.box,\n          a.param_value foo_value,\n          b.param_value bar_value,\n          a.timestamp\n     from some_table a\nleft join some_table b\n       on a.box = b.box\n      and a.timestamp = b.timestamp\n      and b.param_name = 'bar'\n    where a.param_name = 'foo'\n

or, Oracle-style:

\nselect a.box,\n       a.param_value foo_value,\n       b.param_value bar_value,\n       a.timestamp\n  from some_table a,\n       some_table b\n where a.box = b.box (+)\n       and a.timestamp = b.timestamp (+)\n       and a.param_name = 'foo'\n       and b.param_name (+) = 'bar';\n

Where neither is guaranteed, the "approved solution" would be to bring UNION into it. I don't particularly care for that approach (I do know that outer joins effectively are unions). This is what I would do in this case:

\ncreate view boxes_timestamps as\n  select box, timestamp\n    from some_table\n   group by box, timestamp\n\n   select a.box,\n          b.param_value foo_value,\n          c.param_value bar_value,\n          a.timestamp\n     from boxes_timestamps a,\nleft join some_table b\n       on a.box = b.box\n      and a.timestamp = b.timestamp\n      and b.param_name = 'foo'\nleft join some_table c\n       on a.box = c.box\n      and a.timestamp = c.timestamp\n      and c.param_name = 'bar'\n

Or, Oracle-style:

\nselect a.box,\n       b.param_value foo_value,\n       c.param_value bar_value,\n       a.timestamp\n  from boxes_timestamps a,\n       some_table b,\n       some_table c\n where a.box = b.box (+)\n       and a.timestamp = b.timestamp (+)\n       and b.param_name (+) = 'foo'\n       and a.box = c.box (+)\n       and a.timestamp = c.timestamp (+)\n       and c.param_name (+) = 'bar';\n

The other case that you describe, that of taking only the most recent occurence, is tricky. You cannot use a MAX on the timestamp because SQL aggregate functions apply to individual fields. That is, once you take MAX(timestamp), the value that is returned has lost its relation to the row from which it was pulled.

In the straighforward first case of all box/timestamp combinations having both a foo and bar value, the most easily understood way of getting the most recent values of foo and bar is:

\ncreate view most_recent_poll as\n  select box, max(timestamp)\n    from some_table\n   group by box\n\nselect a.box,\n       b.param_value foo,\n       c.param_value bar,\n       a.timestamp\n  from most_recent_poll a,\n       some_table b,\n       some_table c\n where a.box = b.box\n   and b.param_name = 'foo'\n   and a.timestamp = b.timestamp\n   and a.box = c.box\n   and c.param_name = 'bar'\n   and a.timestamp = c.timetamp\n

The other cases are more complex.

If you want to be slick and give yourself a headache, here's something I've done in Oracle to save a join or two:

\nselect a.box,\n       substr(\n              max(\n                  to_char(a.timestamp,'YYYYMMDDHH24MISS')||\n                  to_char(a.param_value)\n              ), 14\n       ) foo_value,\n       substr(\n              max(\n                  to_char(a.timestamp,'YYYYMMDDHH24MISS')||\n                  to_char(b.param_value)\n              ), 14\n       ) bar_value,\n       max(a.timestamp) timestamp\n  from some_table a,\n       some_table b\n where a.box = b.box\n       and a.timestamp = b.timestamp\n       and a.param_name = 'foo'\n       and b.param_name = 'bar';\n

Please note that all these examples will likely take a while to run and may have a nasty impact on the database server.

Also, duplication of foo or bar parameters in any box/timestamp pairing will lead to exponential duplicates in your result sets.

Next time, denormalize a little. Live it up. :-)


Hope this helps,

Mike

Edited one of the queries where I had left-out the timestamp info.

select a.box,
       a.param_value foo_value,
       b.param_value bar_value,
       a.timestamp
  from some_table a,
       some_table b
 where a.box = b.box 
   and a.timestamp = b.timestamp
   and a.param_name = 'foo'
   and b.param_name = 'bar'

   select a.box,
          a.param_value foo_value,
          b.param_value bar_value,
          a.timestamp
     from some_table a
left join some_table b
       on a.box = b.box
      and a.timestamp = b.timestamp
      and b.param_name = 'bar'
    where a.param_name = 'foo'

   select a.box,
          a.param_value foo_value,
          b.param_value bar_value,
          a.timestamp
     from some_table a,
          some_table b
    where a.box = b.box (+)
      and a.timestamp = b.timestamp (+)
      and a.param_name = 'foo'
      and b.param_name (+) = 'bar';

create view boxes_timestamps as
  select box, timestamp
    from some_table
   group by box, timestamp

   select a.box,
          b.param_value foo_value,
          c.param_value bar_value,
          a.timestamp
     from boxes_timestamps a,
left join some_table b
       on a.box = b.box
      and a.timestamp = b.timestamp
      and b.param_name = 'foo'
left join some_table c
       on a.box = c.box
      and a.timestamp = c.timestamp
      and c.param_name = 'bar'

   select a.box,
          b.param_value foo_value,
          c.param_value bar_value,
          a.timestamp
     from boxes_timestamps a,
          some_table b,
          some_table c
    where a.box = b.box (+)
      and a.timestamp = b.timestamp (+)
      and b.param_name (+) = 'foo' 
      and a.box = c.box (+)
      and a.timestamp = c.timestamp (+)
      and c.param_name (+) = 'bar';

create view most_recent_poll as
  select box, max(timestamp)
    from some_table
   group by box

select a.box,
       b.param_value foo,
       c.param_value bar,
       a.timestamp
  from most_recent_poll a,
       some_table b,
       some_table c
 where a.box = b.box
   and b.param_name = 'foo'
   and a.box = c.box
   and c.param_name = 'bar'

select a.box,
       substr(
              max( 
                  to_char(a.timestamp,'YYYYMMDDHH24MISS')||
                  to_char(a.param_value)
              ), 14
       ) foo_value,
       substr(
              max(
                  to_char(a.timestamp,'YYYYMMDDHH24MISS')||
                  to_char(b.param_value)
              ), 14
       ) bar_value,
       max(a.timestamp) timestamp
  from some_table a,
       some_table b
 where a.box = b.box
   and a.timestamp = b.timestamp
   and a.param_name = 'foo'
   and b.param_name = 'bar';