Well, I though I had this resolved in the following answer...
But, when you do the Group By, it doesn't always work. Continuing on the example info from before...
I am working with readings from a meter (like an electric meter), where the readings are on a totalizer, so to get the amount used between readings, you subtract the earlier reading from the later reading.
So, I am trying to do a sql statement that joins in the IMMEDIATE previous reading so you can subtract it.
The table (meter_data) with the essential fields and example data...
reading_id read_date read_time reading
----------- ----------- ----------- ---------
1 2004-08-25 4:00:00 100
2 2004-08-26 5:00:00 150
3 2004-08-26 11:00:00 170
4 2004-08-22 14:00:00 60
SELECT m1.read_date as end_date , m1.read_time as end_time , m2.read_date as start_date , m2.read_time as start_time , m1.reading - m2.reading as amount_used
FROM meter_data m1 inner join meter_data m2 on (m1.read_date > m2.read_date OR (m1.read_date = m2.read_date AND m1.read_time > m2.read_time))
GROUP BY m2.read_date, m2.read_time
If you run this without the GROUP BY statement, you will get 6 records (3 previous records for reading_id of 3, 2 previous records for reading_id 2, 1 previous record for reading_id 1, and none for reading_id 4).
When you apply a GROUP BY, it doesn't have enough information to correctly group by, i.e. it doesn't know which of the 3 returned records for reading_id 3 is the right one (the immediate previously one).
I tried meddling around with timediff to find the smallest timediff, but kept going in circles.
I am trying to keep this in one sql statement, though if I have to, I will do two seperate SQL statments, ordered by read_date,read_time, and offset by 1 with a limit, and run through my math that way.
Any suggestions you have would be much appreciated.