Hi!

We have a database that stores vehicle's gps position, date, time, vehicle identification, lat, long, speed, etc., every minute.

The following select pulls each vehicle position and info, but the problem is that returns the first record, and I need the last record (current position), based on date (datagps.Fecha) and time (datagps.Hora). This is the select:

SELECT configgps.Fichagps, datacar.Ficha, groups.Nombre, datagps.Hora, datagps.Fecha, datagps.Velocidad, datagps.Status, datagps.Calleune, datagps.Calletowo, datagps.Temp, datagps.Longitud, datagps.Latitud, datagps.Evento, datagps.Direccion, datagps.Provincia FROM asigvehiculos INNER JOIN datacar ON (asigvehiculos.Iddatacar = datacar.Id) INNER JOIN configgps ON (datacar.Configgps = configgps.Id) INNER JOIN clientdata ON (asigvehiculos.Idgroup = clientdata.group) INNER JOIN groups ON (clientdata.group = groups.Id) INNER JOIN datagps ON (configgps.Fichagps = datagps.Fichagps) Group by Fichagps;

I need same result Im getting, but instead of the older record I need the most recent
(LAST datagps.Fecha / datagps.Hora).


How can I accomplish this?

Thanks a lot!

in your query, you have GROUP BY -- is this an attempt to get only one datagps row?

Looks like what you want is pretty much selecting the max rows per each group. I've came up with similar issues and have posted a blog post for it here: Microshell Optimizing SQL that selects the max/min/etc from a group.

Hope it helps.