I have a set of data which is a bunch of
readings taken at various dates, eg:
Siteid | Date | Measurement
1 | 2008-11-20 | 28.432
1 | 2008-12-04 | 37.681
1 | 2009-01-12 | 59.529
1 | 2009-02-04 | 67.831
What I need is to be able to match each date's reading against the reading for the max previous date for each site, ie,
The reading for 2009-02-04 needs to be matched to the reading for 2009-01-12, and the reading for 2009-01-12 in turn needs to be matched against 2008-12-04. Basically I need to be able to evaluate the measurement's growth rate between the two dates.
At the moment I have:
SELECT * FROM readings AS curr INNER JOIN readings AS prev ON curr.siteid=prev.siteid AND curr.date>prev.date WHERE curr.siteid=1
Which does match them correctly, but gives ALL the previous reading dates, when I just need the one record with the maximum previous date.
I've messed about with some max() subqueries, but ended up getting further away from a result.