#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    Mysql MAX problem


    Hi all,

    I was hoping somebody could point me in the right direction. The MAX(RECEIVED_TIME) doesn't return the most recent entry.

    I am trying to get the most recent entry for each MMSI (name of boats - the boats are entered into the database every 3 minutes). I only want boats that were seen in the last 40 minutes, but there could be 10 entries within that time for each boat, I only want the most recent. $last40mins=time()-2400; the received time is stored unixtime


    SELECT MAX(RECEIVED_TIME) as RECEIVED_TIME,MMSI,LAT,LNG,NAME,SPEED,HEADING
    FROM AIS_STORE2
    WHERE RECEIVED_TIME > $last40mins
    GROUP BY MMSI

    This kinda works but it doesn't return the most recent entry. Any help would be much appreciated.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by Morespuds
    Hi all,

    I was hoping somebody could point me in the right direction. The MAX(RECEIVED_TIME)

    this will work for you .

    Code:
    select temp.* from (SELECT RECEIVED_TIME as RECEIVED_TIME,MMSI,LAT,LNG,NAME,SPEED,HEADING 
    FROM AIS_STORE2 
    WHERE RECEIVED_TIME > NOW() - INTERVAL 40 MINUTE 
    order by RECEIVED_TIME desc) as temp 
    GROUP BY MMSI
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Techanalyst
    this will work for you .
    nope

    this will work way better --
    Code:
    SELECT t.received_time
         , t.mmsi
         , t.lat
         , t.lng
         , t.name
         , t.speed
         , t.heading 
      FROM ( SELECT mmsi
                  , MAX(received_time) AS  max_received_time
               FROM ais_store2 
              WHERE received_time > $last40mins 
             GROUP 
                 BY mmsi ) AS m
    INNER
      JOIN ais_store2 AS t
        ON t.mmsi = m.mmsi
       AND t.received_time = m.max_received_time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    nope

    this will work way better --
    Code:
    SELECT t.received_time
         , t.mmsi
         , t.lat
    did you test the code this is not working and also consider the run time for both

    got error #1054 - Unknown column '$last40mins' in 'where clause'
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Techanalyst
    did you test the code this is not working
    what?

    Originally Posted by Techanalyst
    and also consider the run time for both
    a fast query that produces the wrong answer is ~not~ better than a slower query that produces the right answer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    what?

    a fast query that produces the wrong answer is ~not~ better than a slower query that produces the right answer

    yours max(date) and my order by date desc ....what is the difference i cant see any difference. how my query produce wrong results ...anyway thanks for your query i got new logic to do the same.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Techanalyst
    yours max(date) and my order by date desc ....what is the difference i cant see any difference.
    actually, yes, you can see a difference if you look closely

    you need to read this --
    Originally Posted by da manual
    MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.
    -- http://dev.mysql.com/doc/refman/5.0/...xtensions.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    you need to read this --

    Thank you for the info.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0
    Thanks guys, I ended up using r937's solution but thanks to both of you. It's great to get help when you're really stuck with something. Much appreciated.

IMN logo majestic logo threadwatch logo seochat tools logo