[SOLVED] Acquiring the most recent updates across a group of units.

[Kurrel]

Hi everyone,

 

I've a table that has all the status updates of several dozen units and I need to create a list of each unit's 3 most recent updates.

 

MySQL server versio : 5.0.27-community-nt

 

$sql = "select dtTime , liGPSID , fLatitude,fLongitude from GPSData where liGPSID > " . $lastpoint . " and dtTime > '$start' ORDER by dtTime";

This currently pulls all the records from the database, ordered by most recent.  This can be over 50k records, though, sometimes a 100 for each unit.

 

The table structure : (I can't do a direct pull right now, I'll try update this asap.)

 

Id, dtTime, liGPSID, fLatitude, fLongitude, truckid, userid, deleted

 

I've tried limiting by time, but some of the units are updated several times a minute and some are updated only once every few hours.

 

I tried a group and order combination, but they don't return all the other details included in those records.

 

Is there any SQL command to return the most recent update, and from that the 3 most recent?  They're all stamped with unix dates.

 

Please, help?

 

Kurrel.

[fenway]

That's a tricky query... group by / order by / limit don't really work well when you're trying to get the max/top N amongst a group within a table.  The only workaround that I can think of at the moment is to "ORDER BY liGPSID ASC, dtTime DESC", and then -- using user variables -- count to 3, and make a new expression column in the select list with a 0 or 1, depending on if you've hit 3 or not.  Then, use the HAVING clause to "filter" the results.  Not pretty.

[Kurrel]

I've started building that, and it looks good so far.

 

Thank you, I'd not seen the 'HAVING' before.