Hy there,

I'm working on a problem I can't seem te solve in MySQL.

I have a table with snapshots of cummulative values. The information I need is the difference of those values within a timeframe.
At the moment I use PHP to calculate thoses difference because I can't seem te get the min and max date from the table AND the values corresponding to those records.

Code:
SELECT fms1.* 
FROM fms1 
WHERE
AND fms1.gps_unit_id = 114 
AND fms1.created >= "2013-03-01 00:00:00" 
ORDER BY fms1.created ASC 
LIMIT 1
) UNION (
SELECT fms1.* 
FROM fms1 
    WHERE fms1.gps_unit_id = 114 
    AND fms1.created >= "2013-03-01 00:00:00" 
    AND fms1.created <= "2013-03-01 23:59:59" 
    ORDER BY fms1.created DESC 
LIMIT 1
)
This way I get two rows returned for which I calculate the difference for each of those values. (say fms1.odometer, fms1.idle_time and so on)

I tried something like this:

Code:
SELECT
    fms1.*,
    max(fms1.created) AS MaxDate,
    min(fms1.created) AS MinDate
FROM fms1
WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" 
AND fms1.gps_unit_id = 114
This does return the correct MinDate and MaxDate for the records on that day (over a 100), but it only returns the complete record for the MinDate. Which in this case is the correct one. You cannot sort the records by id, because causality is not guarantied.

The thing I can't get my head around is how to select complete records based on the outcome of funtions like min() and max(). For instance if you use
Code:
SELECT
    max(fms1.created) AS MaxDate,
    min(fms1.created) AS MinDate
FROM fms1
WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" 
AND fms1.gps_unit_id = 114
I do get the correct dates, but how to get the records belonging to those values? You can't use min() and max() in subqueries...

Many thanks!