How to group and order results

[Hieronymus]

Hi,

I want to select the first and last record within a time period (by created field) for each unit (left join from other table) how does that work?

the table with FMS1 data of many units
has many fields including id, gps_unit_id, created, and a bunch of data fields
a gps_units table has
id and user_id

so for one unit I would do:

(
SELECT `fms1`.`gps_unit_id` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` >= '2012-03-26 00:00:00'
ORDER BY `fms1`.`created` ASC
LIMIT 1
)
UNION (

SELECT `fms1`.`gps_unit_id` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` <= 'NOW'
ORDER BY `fms1`.`created` DESC
LIMIT 1
)

But I want it for all units from a user.

So I started with

SELECT `fms1`.`gps_unit_id` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
LEFT JOIN gps_units AS GpsUnit ON GpsUnit.id = fms1.gps_unit_id
WHERE GpsUnit.user_id = 61
AND `fms1`.`created` >= '2012-03-26'
GROUP BY `fms1`.`gps_unit_id`

But if I change the created clause to <= 'NOW' It somehow gets all units and returns just te very first recors. I can't figure out how to sort them the other way round or how to make the correct join.

Any ideas?
(Actually I need to calculate the difference between the to times, so if MySQL could do that in one go, that would be sublime!)

[r937]

Actually I need to calculate the difference between the to times...

so you don't actually need "first and last record"? just earliest and latest created values for each unit?

what a difference that makes!!!

[Hieronymus]

indeed, I need the earliest and latest record WITHIN a givin period, say the previous week or the current week. For each unit.

[r937]

indeed, I need the earliest and latest record WITHIN a givin period, say the previous week or the current week. For each unit.

i'm not sure i made the distinction clear enough

i understand earliest and latest, but do you need the entire record, i.e. the whole row?

or do you just need the created datetime from the earliest and latest row? per unit of course

remember, you said "Actually I need to calculate the difference between the to times"

seems to me like retrieving the entire record (which would involve extra subqueries) is unnecessary, if you can simply get the earliest and latest datetime values

[Hieronymus]

Ah sorry for the inconvience. I hope this makes it all clear:

The table 'fms1' gets filled with a new record by every unit about every minute. For a few datafields in each record I want to calculate the difference (the actual increase) within a given time period (last week, or the current week, ...).
Ofcourse selecting the fields and all the records in the timeperiod is easy (using BETWEEN on the created field). But then I would just discard 99% of the data I just retrieved (every record besides the first and the last one in the result set). I was hoping MySQL could solve this for me. It's not nessesary for mysql to calculate the difference between the fields in the records, but hey, if it can ADD and COUNT, maybe there's way to do this... I would be very happy if i would get 2 records (the first and last in the given time period) for every unit of a user. If those records are complete rows that's fine, but I'm going to use only 4 of the 14 fields, so a subset makes the whole thing even better (if thats actually easy to do...)

I hope that helps...

[r937]

...I'm going to use only 4 of the 14 fields

you realize that this is a significant change from your previous statement about calculating the difference between the two times

yes, now you do need the rows for the earliest and latest times, if you are doing additional calulations besides just the difference between the two times

correct?

okay, what are the 4 columns you need?

[Hieronymus]

Hi,

Thanks for helping out. from the fms1 table I need the info from the fields:
odometer (double), total_fuel (float), engine_hours (float), created(datetime) and ofcourse the gps_unit_id (int(10))

for the first 4 fields I want to calculate the difference within a given time period.

In the first post I posted my sollution for a single unit:

(
SELECT `fms1`.`gps_unit_id` , `odometer` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` >= '2012-03-26 00:00:00'
ORDER BY `fms1`.`created` ASC
LIMIT 1
)
UNION (

SELECT `fms1`.`gps_unit_id` , `odometer` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` <= 'NOW'
ORDER BY `fms1`.`created` DESC
LIMIT 1
)

This wil result in:

gps_unit_id 	odometer 	total_fuel 	engine_hours 	created
76 	      309850.575 	       117334 	       9197.5 	2012-03-26 17:46:09
76 	      310753.895 	       117647 	       9222.85 	2012-03-27 18:51:19

with PHP code I would calculate the different values between the odometer, total_fluel, engine_hours and created (the time between the two records (which will be smaller then the time difference between the two bounds of the timeperiod))

I hope this makes sense now. The problem I have is that I have no idea how to do this for multiple units (all the units coupled to a single user) and make the most of mySQL power of selecting only data I need. that is:
- I dont want all the records in a time period, just the first and last in a timeperiod (see example above)
- Do some or all calculation by mySQL if this is efficient.

In the first post said I tried this with a LEFT JOIN, GROUP BY and ORDER BY, but I couldn't make it work. and that was even without letting mysql doing the calculation.

[r937]

SELECT latest.gps_unit_id 
     , latest.odometer - earliest.odometer         AS diff_odometer
     , latest.total_fuel - earliest.total_fuel     AS diff_total_fuel
     , latest.engine_hours - earliest.engine_hours AS diff_engine_hours
     , TIMEDIFF(latest.created - earliest.created) AS diff_created
  FROM ( SELECT gps_unit_id
              , MIN(created) AS min_created
              , MAX(created) AS max_created
           FROM fms1
          WHERE created BETWEEN '2012-03-26' AND NOW() 
         GROUP
             BY gps_unit_id ) AS mm
INNER
  JOIN fms1 AS earliest
    ON earliest.gps_unit_id = mm.gps_unit_id
   AND earliest.created     = mm.min_created             
INNER
  JOIN fms1 AS latest
    ON latest.gps_unit_id = mm.gps_unit_id
   AND latest.created     = mm.max_created

lovely in its simplicity, isn't it

elegant sql is a joy forever

[Hieronymus]

SELECT latest.gps_unit_id 
     , latest.odometer - earliest.odometer         AS diff_odometer
     , latest.total_fuel - earliest.total_fuel     AS diff_total_fuel
     , latest.engine_hours - earliest.engine_hours AS diff_engine_hours
     , TIMEDIFF(latest.created - earliest.created) AS diff_created
  FROM ( SELECT gps_unit_id
              , MIN(created) AS min_created
              , MAX(created) AS max_created
           FROM fms1
          WHERE created BETWEEN '2012-03-26' AND NOW() 
         GROUP
             BY gps_unit_id ) AS mm
INNER
  JOIN fms1 AS earliest
    ON earliest.gps_unit_id = mm.gps_unit_id
   AND earliest.created     = mm.min_created             
INNER
  JOIN fms1 AS latest
    ON latest.gps_unit_id = mm.gps_unit_id
   AND latest.created     = mm.max_created

Hi, sorry for not getting back to you urlier. Moving house took a lott of time out of my sceduele.
It does looks elegant to me, but I don't think my mySQL skills are good enough to fully understand this one...

I'm not sure this does what I want it to do. I get results like this:

gps_unit_id 	diff_odometer 	diff_total_fuel 	diff_engine_hours 	diff_created
76 	1667.94500000001 	704    	57.650390625 	88:01:01
79 	1147.35              	503.5 	45.6000061035156 	76:17:04
79 	1147.35              	503.5 	45.6000061035156 	76:17:04
79 	1147.35              	503.5 	45.6000061035156 	76:17:04
79 	1147.35              	503.5 	45.6000061035156 	76:17:04
80 	1432.22499999998 	682    	62.7998046875 	        88:27:27
81 	1329.71500000003 	603    	44.1005859375 	      83:09:57
90 	1258.73999999999 	672    	38.89990234375    	82:46:55
91 	1364.57000000001 	688    	41 	                       83:07:57
92 	1886.78              	785    	40 	                       84:33:03

(hope that's readable.)

I changed the dates to 2012-04-02 and NOW to get the results for this week.
also timediff() works with two commaseperated values.

You should know, there are currently 7 units submitting data into the fms1 table; 76, 79, 80, 81, 90, 91 and 92
4 belong to one user, and 3 to another.

If I just run the inside select:

SELECT gps_unit_id, MIN( created ) AS min_created, MAX( created ) AS max_created
FROM fms1
WHERE created
BETWEEN '2012-04-02'
AND NOW( )
GROUP BY gps_unit_id

I get:

gps_unit_id 	min_created 	max_created
76 	2012-04-02 04:14:19 	2012-04-05 20:15:20
79 	2012-04-02 14:02:40 	2012-04-05 18:19:44
80 	2012-04-02 04:34:55 	2012-04-05 21:02:22
81 	2012-04-02 05:07:14 	2012-04-05 16:17:11
90 	2012-04-02 06:57:33 	2012-04-05 17:44:28
91 	2012-04-02 05:53:56 	2012-04-05 17:01:53
92 	2012-04-02 04:33:26 	2012-04-05 17:06:29

except that it's for all the units. In this I don't understand the group by function. If I leave it out, I just get one record:

gps_unit_id 	min_created 	max_created
76 	2012-04-02 04:14:19 	2012-04-05 21:02:22

note the difference in times for the min_created and max_created fields.
for unit 76 the dates should be: 2012-04-02 04:14:19 and 2012-04-05 20:15:20
found by sorting (SELECT *FROM `fms1`WHERE `created`BETWEEN '2012-04-02' AND NOW( ) AND `gps_unit_id` =76 ORDER BY `fms1`.`created` DESC LIMIT 0 , 30 ) ASC and DESC

We can select the units beloging to one user with a inner join

INNER JOIN gps_units ON gps_units.id = fms1.gps_unit_id

So, each user has units
each unit submits data to the fms1 table.
I like for each unit belonging to a user the difference between first and last record within a time period. I think you got that last part right, but somehow selecting or grouping the right units is not working as it should.

Please let me know what kind of info you need to fix this.

Many thanks in advance

Jeroen

[r937]

also timediff() works with two commaseperated values.

thanks for spotting that

as for the rest of it, i am sorry, TL;DR