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.
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:
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
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!