Mysql MAX problem

Dev Shed Forums Sponsor:

March 7th, 2013, 06:38 AM

Morespuds

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 54 m 52 sec
Reputation 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.

March 7th, 2013, 09:43 AM

Techanalyst

Registered User

Join Date: Mar 2013

Posts: 11

Time spent in forums: 5 h 32 m 26 sec
Reputation Power: 0

Quote:

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

March 7th, 2013, 10:25 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 14 sec
Reputation Power: 4140

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

March 7th, 2013, 11:35 AM

Techanalyst

Registered User

Join Date: Mar 2013

Posts: 11

Time spent in forums: 5 h 32 m 26 sec
Reputation Power: 0

Quote:

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'

March 7th, 2013, 12:21 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 14 sec
Reputation Power: 4140

Quote:

Originally Posted by Techanalyst

did you test the code this is not working

what?

Quote:

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

March 7th, 2013, 12:58 PM

Techanalyst

Registered User

Join Date: Mar 2013

Posts: 11

Time spent in forums: 5 h 32 m 26 sec
Reputation Power: 0

Quote:

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.

March 7th, 2013, 02:24 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 14 sec
Reputation Power: 4140

Quote:

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 --

Quote:

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...extensions.html

March 7th, 2013, 02:36 PM

Techanalyst

Registered User

Join Date: Mar 2013

Posts: 11

Time spent in forums: 5 h 32 m 26 sec
Reputation Power: 0

Quote:

Originally Posted by r937

you need to read this --

Thank you for the info.

March 7th, 2013, 02:36 PM

Morespuds

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 54 m 52 sec
Reputation 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.

Mysql MAX problem

Developer Shed Advertisers and Affiliates