SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to group and order results

    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:
    Code:
    (
    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
    Code:
    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!)
    What good are one-liners if they don't
    fit.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Hieronymus View Post
    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!!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    indeed, I need the earliest and latest record WITHIN a givin period, say the previous week or the current week. For each unit.
    What good are one-liners if they don't
    fit.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Hieronymus View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    What good are one-liners if they don't
    fit.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Hieronymus View Post
    ...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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    (
    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:
    Code:
    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.
    What good are one-liners if they don't
    fit.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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
    Code:
    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
    What good are one-liners if they don't
    fit.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Hieronymus View Post
    also timediff() works with two commaseperated values.
    thanks for spotting that

    as for the rest of it, i am sorry, TL;DR
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •