1

I have 2 tables each of which have a timestamp column. How do I query for each row in A, the first preceding and following timestamps in B ?

I want:

A.id A.timestamp first_preceding(B.timestamp) first_following(B.timestamp)

Hendekagon
  • 4,565
  • 2
  • 28
  • 43

1 Answers1

2

I'd try this:

SELECT DISTINCT a.id, a.timestamp, b0.timestamp, b1.timestamp
FROM a, b b0, b b1
WHERE
b0.timestamp = (SELECT MAX(timestamp) FROM b WHERE timestamp < a.timestamp)
AND b1.timestamp = (SELECT MIN(timestamp) FROM b WHERE timestamp > a.timestamp);
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Hew Wolff
  • 1,489
  • 8
  • 17
  • You can get the IDs of the B rows by adding `, b0.id, b1.id` to the list of columns. – Hew Wolff Nov 07 '12 at 01:02
  • yes but I need the ids of rows in b for which the b.timestamp is a maximum or minimum, not just all ids in the cross product – Hendekagon Nov 08 '12 at 01:15
  • I think the WHERE clause picks out the relevant rows for you. That's why `b0.timestamp, b1.timestamp' should work, and why I expect that the `id` column is available in the same way. Are you seeing something different? I suppose that if there are multiple rows in B satisfying (for example) the `b0` test, then since there are multiple `b0.id` values, the `DISTINCT` would give you a separate row for each. Is that the problem? – Hew Wolff Nov 08 '12 at 03:07
  • wow I didn't know you could do that. I'm having trouble seeing what's going on here - why can I select b0.id even though b0 is max(timestamp) ? – Hendekagon Nov 08 '12 at 04:52
  • `b0` is a row in the table `b` (that's what the `b b0` means), so once you have that row you can get any column you want. The `WHERE` clause just makes sure that `b0`'s timestamp is as large as you want it. Also, note that in the subquery, `timestamp` is short for `b.timestamp` since you're selecting from `b`. Does that help? – Hew Wolff Nov 10 '12 at 19:25
  • it reads as if b0 and b1 are tables of 1 row each - how can one know that they are rows not tables ? – Hendekagon Nov 11 '12 at 23:11
  • I'm not quite following you, but what I just said about `b b0` should make it clear that `b0` is a row. – Hew Wolff Nov 12 '12 at 04:33